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

[Manojkumar]

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 1	Group ID 2	Group ID 3	Month	Result
a	a	b	Jan	1
a	a	b	feb	1
a	b	c	Mar	3

The result i want is

Group ID1	Group ID2	Group ID3	Month	Results
a	a	b	Jan	1
a	a	b	Feb	1
a	a	b	Mar	0
a	b	c	Jan	0
a	b	c	Feb	0
a	b	c	Mar	3

Please help, thanks in advance

[adamorse · Accepted solution]

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 · Accepted solution]

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]

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