Merge rows and particular part of it

Four Stars

Merge rows and particular part of it

Hi everybody,

 

I don't figure out how can I manage to merge rows from an excel file into one unique rows with merging a particular part of the row and concatenate the text it contains.

Here's an example :

 

ID | OPEN_DATE | CLOSE_DATE | LAST_LOG_DATE | LOG_TEXT

1   | 01/01/1970    | 10/01/1970      | 01/01/1970     | random text

1   | 01/01/1970    | 10/01/1970      | 02/01/1970     | some new text

1   | 01/01/1970    | 10/01/1970      | 10/01/1970     | some new text again

 

I want to transform the file above into the following format :

 

ID | OPEN_DATE | CLOSE_DATE | LAST_LOG_DATE | LOG_TEXT

1   | 01/01/1970    | 10/01/1970      | 10/01/1970     | random text some new text some new text again

 

I want to take the MAX(DATE) from LAST_LOG_DATE column and concatenate the text of the LOG_TEXT one.

 

So, do you know how can I manage to do this please ?

 

Thanks in advance


Accepted Solutions
Sixteen Stars

Re: Merge rows and particular part of it

Use a tAggregateRow component. Set up the fields as below....

 

ID set as your group key (in the top box) ....I'm assuming you are grouping by ID

OPEN_DATE set in the Operations section as MIN ....assuming you want the earliest date

CLOSE_DATE set in the Operations as MIN or MAX ....depending on what you want to show. You can also set first or last if you want the value to be selected by the order in which it hits the component.

LAST_LOG_DATE set in the Operations section as MAX

LOG_TEXT set in the Operations section as List.

 

You may need to tweak this as I suspect you have not given the full info here.


All Replies
Sixteen Stars

Re: Merge rows and particular part of it

Use a tAggregateRow component. Set up the fields as below....

 

ID set as your group key (in the top box) ....I'm assuming you are grouping by ID

OPEN_DATE set in the Operations section as MIN ....assuming you want the earliest date

CLOSE_DATE set in the Operations as MIN or MAX ....depending on what you want to show. You can also set first or last if you want the value to be selected by the order in which it hits the component.

LAST_LOG_DATE set in the Operations section as MAX

LOG_TEXT set in the Operations section as List.

 

You may need to tweak this as I suspect you have not given the full info here.

Four Stars

Re: Merge rows and particular part of it

Thank you very much !