0

I have a case where I am reading data in through a table input step. The values that are read in would be:

agent, sub agent, merchant, total
1,      2,         2222,     10
2,      2,         2343,      4
1,      3,         1212,      1

What I am trying to accomplish is checking to see if there is an instance where agent does not have the same value as a sub agent. So if agent 1 does not have sub agent 1 then I need to create a row to have:

agent, sub agent, merchant, total
1,        1,        null,    0

I am not really sure how I could possibly generate this single row as its own row. I have attempted several methods using filter row and creating constants but all attempts have either overwrote all existing entries that did not match and changed them to be sub agent 1 or created agent_1, sub agent_1, merchant_1, and total_1 fields.

bolav
  • 6,938
  • 2
  • 18
  • 42
John B
  • 159
  • 3
  • 14
  • For me this looks like a general data quality issue. Wouldn't it be the best to run an update statement taking care of it? You might execute this before you use the table input step. – stb Dec 28 '15 at 18:15
  • Actually, this has more to do with the type of reporting than the data itself. It would be far too complicated to explain (and lengthy) but in summary, sub agents earn agents money and an agent can earn his own money too. So an agent can be his own sub agent in that sense. However, if the agent does not earn himself money for a certain month we still need to have the agent/sub agent relation of the agent and himself as the sub agent. – John B Dec 28 '15 at 19:33

2 Answers2

0

If you copy the streams in three, do a stream lookup and check if the row with same agent and sub agent exists, and if it doesn't add the row that doesn't exists.

Here is my output:

agent   sub merchant    total
1   2    2222    10
2   2    2343    4
1   3    1212    1
1   1   <null>   0

Picture of transformation

bolav
  • 6,938
  • 2
  • 18
  • 42
0

I think this solution easier. Might need to correct sql query (I use postgres) enter image description here

simar
  • 1,782
  • 3
  • 16
  • 33