community
cancel
Showing results for 
Search instead for 
Did you mean: 

community Lounge

Get to know your fellow analytics buffs and discuss a wide range of non-product related topics!
Announcement | Get certified today - take the Alteryx Designer Core and Advanced exams on-demand now!
SOLVED

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

Meteor

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

Asteroid

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.

 

 

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!

Meteor

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