Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

General Discussions

Discuss any topics that are not product-specific here.
SOLVED

Update column value as 0 if value(from predefined list) is missing in another column

Manojkumar
8 - Asteroid

Hi Team,

 

Someone please help me with the following issue.

I have 2 tables, 1 with reference values for field "Month".

 

Table 1:

Month
Jan

Feb

Mar

... and it goes on

 

Second table (shown below)can have any value from table1 for "Month", but all the values should be there for group of "GroupID 1,2,3", if not the missing values should be added by adding row for each group, with Result as 0

 

Table 2:

Group ID 1Group ID 2Group ID 3MonthResult
aabJan1
aabfeb1
abcMar3

 

The result i want is

Group ID1Group ID2Group ID3MonthResults
aabJan1
aabFeb1
aabMar0
abcJan0
abcFeb0
abcMar3

 

Please help, thanks in advance

3 REPLIES 3
adamorse
9 - Comet

What you can do is use "Append Fields" tools to generate a list of all the records you expect to have values for, join them to the actual table, and use the Right output on the join to check for missing values and add a value of 0. See attached workflow for ideas on how this could work.

 

 

TrevorY
Alteryx
Alteryx

I'm assuming that in this case the Table 1 will normally contain group ID values, and these need to be correctly matched to Table 2 otherwise they receive a value of "0". If that's the case I think the attached workflow will help. Here you are joining not only on month within the data, but also joining by these group IDs. All matches will match perfectly and mismatches will receive a value of "0" as a result. I hope this helps!

Manojkumar
8 - Asteroid

Hi, Thanks for the workflow. it worked fine.

i also tried, assigning 0 to all months and just appended it in month column and did a transpose.

That solved it.

 

Thanks

Labels