topic Re: Converting Multiple Rows to One Row with Conditional Logic in Talend Studio

Converting Multiple Rows to One Row with Conditional Logic

Anonymous — Fri, 25 Nov 2016 18:06:50 GMT

I'm looking for some help with my TMap converting a table that looks like this:

Period, Type, Value
1001, 1, .5
1001, 2, .15
1001, 3, .25

To this:

Period, Value1, Value2, Value3
1001, .5, .15, .25

Not all 3 values are guaranteed to exist (in fact, 3 usually doesn't exist), and only values 1 to 3 are possible (though bad data happens). it's actually a pretty big TMAP with 10 or so tables and some weird year logic, but this is the bit that doesn't work.

Re: Converting Multiple Rows to One Row with Conditional Logic

Anonymous — Fri, 25 Nov 2016 18:40:48 GMT

Currently I have each of the values set up with a conditional, like this:
out_row.value1: in_row.Type == 1 ? in_row.Value : null
out_row.value2: in_row.Type == 2 ? in_row.Value : null
out_row.value3: in_row.Type == 3 ? in_row.Value : null
The problem is that I only end up with one value per period: the last value processed, as it nulls out the other values.

Re: Converting Multiple Rows to One Row with Conditional Logic

JR1 — Fri, 25 Nov 2016 21:13:06 GMT

Have you looked at the tDenormalize component? Is this what you are looking for?

Re: Converting Multiple Rows to One Row with Conditional Logic

Anonymous — Fri, 25 Nov 2016 21:17:47 GMT

I tried denormalize, but as far as I can tell it can only deal with delimited data, it can't seem to handle any sort of conditional logic.
Currently, I'm trying to import the source table 3 times, each into a different map which then assigns a different value, with conditional logic for whether the value should be included. I'll then unite these, and then sum for each period with aggregate row. It's the conditional logic which is the real pain here; nothing seems to be able to handle conditions.