Pentaho Kettle Group by with type concatenate produces duplicates

Question

I have following Excel file:

My end result should looks like (example of output file for first entry):

<?xml version="1.0" encoding="utf-8"?>
<asset>
    <asset_feature key="ItemID" value="12000"/>
    <asset_feature key="Name" value="Name of the first item"/>
    <asset_feature key="Price" value="1020.0"/>
</asset>

In order to achieve it on initial steps I use Row Normaliser step and after it Group by with type Concatenate strings separated by ,. I am expecting to have this:

<asset_feature key="ItemID" value="12000"/>, <asset_feature key="Name" value="Name of the first item"/>, <asset_feature key="Price" value="1020.0"/>

and use technic from XML Add - creating multi level XML files example for >, < replacement, as: var xmlOutput=xmlOrderListNew.replace(">, <","><");, but instead I get following result back (3 duplicated rows):

<asset_feature key="ItemID" value="12000"/>, <asset_feature key="Name" value="Name of the first item"/>, <asset_feature key="Price" value="1020.0"/>
<asset_feature key="ItemID" value="12000"/>, <asset_feature key="Name" value="Name of the first item"/>, <asset_feature key="Price" value="1020.0"/>
<asset_feature key="ItemID" value="12000"/>, <asset_feature key="Name" value="Name of the first item"/>, <asset_feature key="Price" value="1020.0"/>

so, to fix it I have to put extra transformation step for getting just Unique rows. Transformation picture:

Question is: how to fix Group by step to don't get duplicates without extra Unique rows step, as XML Add - creating multi level XML files doesn't have such issue?

Optional question 1: is there any simple way for concatenation in order to avoid replace(">, <","><") step later?

Optional question 2: How to make pretty XML (with indentation) formatting?

Example Excel & transformation are here

Thank you in advice for your help.

Use a `Concatenate string separated by` (without coma) and do not put anything as `value`. — AlainD, Mar 15 '18 at 09:23
You can put any function in a javascript step. In particular you can copy a xml pretty printer, for example https://gist.github.com/sente/1083506/d2834134cd070dbcc08bf42ee27dabb746a1c54d. — AlainD, Mar 15 '18 at 09:28
Thank you @AlainD. All proposed by you solutions work. For Pretty print I have found out another way with XSLT here: https://stackoverflow.com/questions/376373/pretty-printing-xml-with-javascript The rest works. Strange that when I have played with `Include all row` checkbox before it didn't work for me, but now is fine. One more question: what is the performance benefit of `Memory group by` against `Group by` let say on 1M lines with not 3 (like in example) but 100 key-value pairs? — vaa, Mar 15 '18 at 11:06
@AlainD: please, post your comments as an answer which I can accept as a correct one. Thank you once again. — vaa, Mar 15 '18 at 11:24

score 1 · Accepted Answer · answered Mar 16 '18 at 12:16

Question 0 (Avoid duplicate in group by): Un-check the Include all row check box!

Question 1 (Avoid "," in concatenation): Use a Concatenate string separated by (without coma) and do not put anything as value.

Question 2 (Petty print xml/html): You can put any function in a javascript step. In particular you can copy a xml pretty printer, for example formatXML.js

Un-asked question: Use Memory Group by rather than Group by step, unless you have really really huge data.

Pentaho Kettle Group by with type concatenate produces duplicates

1 Answers1