topic Re: How do I select columns with values when using tAggregateRow? in Talend Studio

How do I select columns with values when using tAggregateRow?

Edith1 — Tue, 09 Jun 2020 16:41:38 GMT

This is a JSON result of an API call and I want to combine the data in 1 row just like the 2nd table below. How do I accomplish this? I was playing/testing with a taggregaterow component, but I'm not sure how to select the columns with values after I group by object_id, term_name, type and relationship_id.

OBJECT_ID	TERM_NAME	TYPE	DEFINITION	STATUS	LAST_MODIFIED_DATE	RELATIONSHIP_ID
123_1	VENDOR	TERMS	NULL	ACTIVE	NULL	9_1
123_1	VENDOR	TERMS	MY VENDOR IS...	NULL	NULL	9_1
123_1	VENDOR	TERMS	NULL	NULL	4/30/2020	9_1
123_1	VENDOR	TERMS	NULL	NULL	NULL	9_1

OBJECT_ID	TERM_NAME	TYPE	DEFINITION	STATUS	LAST_MODIFIED_DATE	RELATIONSHIP_ID
123_1	VENDOR	TERMS	MY VENDOR IS...	ACTIVE	4/30/2020	9_1

Re: How do I select columns with values when using tAggregateRow?

akumar2301 — Tue, 09 Jun 2020 17:18:53 GMT

Hello

Select option “ ignore NULL value” and then in function you can select first/last as functions

Re: How do I select columns with values when using tAggregateRow?

Anonymous — Tue, 09 Jun 2020 19:26:53 GMT

Hi,

First, you need to set all NULL values into null. There is difference between NULL and null you have to make sure if the value is NULL convert into null. And in the tAggregate give either first/last function and check-in the ignore null values. You will get the required result.

Don't forget to give kudos/accept the solution when a replay is helpful.

Re: How do I select columns with values when using tAggregateRow?

Edith1 — Tue, 09 Jun 2020 19:29:13 GMT

Thank you for your response. What is the difference between max and first/last functions?

Re: How do I select columns with values when using tAggregateRow?

Anonymous — Tue, 09 Jun 2020 20:03:23 GMT

Since your datatype is the string you cannot choose Max, either you have to choose first or last. Max is applicable if you have datatype Integer. Also in tAggregate you have ignored all the null values so in a group, you will have only one value.