Column splitting with different numbers of filled columns

[tsv_ml_f21]

Dear Community

I have two tables of an accounting system, the one subsystem table is “FI”, the other one is “CO”.
Both tables theoretically show the same information, but in practice there are differences in units and amounts respectively. So this workflow is for a reconciliation of the differences by material with regard to units and amounts.
Both tables I have grouped by material and combined them with a full outer join (by material). In the group node I have calculated the sum of units by material, the sum of amounts by material and as additional useful information for the user of the workflow the (sorted) list of the accounting document numbers concerned (FI and CO have different logic of accounting document numbers). I.e. one of the group by node:

In order to be able to write the results in an excel I have to split the list of document numbers with split collection column and aggregate them by column aggregator

The issue is that the one material has one document number, another one has i.e. 7 oder 19 document numbers concerned. So I have filled the ‘?’ by ‘nothing’ with missing value,

but nevertheless I get lots of delimiters in the output excel file:

I am not able to ‘delete’ the ‘empty’ columns per row.
This does not look nice; I would like to have the list in the concatenate column with delimiter commas just between document numbers, but no delimiter after the last doc number per row.

Does anyone has a (‘dynamic’ per row) solution for me?

Thanks a lot and best regards

[aworker]

Hi @tsv_ml_f21

A string manipulator could be a solution using the following rule:

replace(removeDuplicates(strip(replace( $Concatenate$, ",", " "))), " ", ",")

This is the result of my test:

Hope this helps.

Best regards

Ael

[Thyme]

Ael seems to have a working solution for you. I might be able to provide a shortcut though:

Do you need to aggregate the “Belegnummern” as List? To me it looks like “concatenate” or “unique concatenate” could work as well, depending on what else you do with the list. You won’t have to deal with splitting the collection if that works for you.
If you sort the input of the GroupBy then the concatenation will keep that order.

That’s just a guess though, I can’t know for sure without the workflow and some (dummy) data.

[tsv_ml_f21]

@aworker

replace(

Thanks a lot, this is a great formula (very complex to me, but I can follow it now).

Best regards

[tsv_ml_f21]

Thanks a lot, this is a great idea; I have just changed the one GroupBy node with unique concatenate, and this looks good !