topic Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields in Talend Studio

(tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

mobmsc2 — Mon, 04 Mar 2019 17:34:13 GMT

I would like to clean up some duplicate rows so that I combine all available data for duplicate rows into 1 but also not replicate data that already exists.

I'm not sure which component (tAggregate,tDenormalise,tPivotToColumns) would suit best as they all seem to involve combining all values found or selecting the first or last value. Its not safe to assume that all new data only appears in the 1st occurrence

Is there a way to only execute the combine task when "new" information is detected without alot of custom logic testing string lengths and null values?

Sample data

ID|Name|Country|Town

1|Tom| Spain|

1|Tom|null|Madrid |

2| |Germany|Berlin

2|John| Germany|

3|Paul| |Glasgow

3|Paul|Scotland|

and end up with

ID|Name|Country|Town

1|Tom|Spain|Madrid |

2|John| Germany|Berlin

3|Paul|Scotland|Glasgow

not

ID|Name|Country|Town

1|Tom;Tom|Spain|Madrid |

2|;John| Germany;Germany|Berlin

3|Paul;Paul|;Scotland|Glasgow;Glasgow

Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

DataTeam1 — Mon, 04 Mar 2019 19:12:37 GMT

Did you try tAggregate with MAX function insted of first or last?

Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

akumar2301 — Mon, 04 Mar 2019 22:08:44 GMT

Is it possible that 2nd occurrence will have different info then the 1st

2| |Germany|Berlin

2|John| France|

2|John| France|Paris

Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

Anonymous — Tue, 05 Mar 2019 05:49:05 GMT

Hi,

I hope you are looking for this solution.

tMap will remove any hardcoded null from the input data.

For example,

Relational.ISNULL(row1.name) || row1.name.equalsIgnoreCase("null")?null:row1.name

aggregation can be done like below.

Hope your issue is resolved

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

mobmsc2 — Mon, 11 Mar 2019 16:47:13 GMT

Hi @nthampi

Can you explain what the MAX function does with text data?

Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

DataTeam1 — Mon, 11 Mar 2019 18:34:52 GMT

Max in text case means the last one in alphabetical order

For example when you have 3 values: 'P', 'B', 'L', null, 'H'

max => 'P'

min => 'B'

Null is omitted in functions max and min

Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

Anonymous — Tue, 12 Mar 2019 00:45:35 GMT

Thanks @DataTeam for pitching in.

@Moe , Could you please mark the topic as solution provided since we have answered your query?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

mobmsc2 — Tue, 12 Mar 2019 10:09:25 GMT

So your process
Step 1 if row value is null OR equals the string null in whatever case possible RETURN null or the value
Step 2 Take the max value excluding nulls from what ever is found when joining by ID.

I had a look at the tAggregate docs and it doesn't explain what the functions count, min, max, avg, sum, first, last, list, list(objects), count(distinct), standard deviation do or what is acceptable input data types. I thought inputs had to be numeric

Re: (tAggregate,tDenormalise,tPivotToColumns) which is best at handling blank fields

Anonymous — Wed, 13 Mar 2019 19:14:14 GMT

Hi,

If it is a String, min and max functions work in alphabetical order. Other functions are not needed in your use case.

Are you facing any issue in current solution? I hope it is covering your use case.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂