Removing Cartesian product while loading data

[mkiran_18]

Hi All,

Currently we have data as per below screenshot.

Could you please help me on how to convert the data as per below screenshot:

Current Output in table box:

Required Output:

Please find the attached excel file and qvw for reference.

Thanks

[petter · Accepted solution]

You can add another preceding load to achieve your goal like this:

TABLE1:
LOAD
  ID, FIELD_1_NEW, FIELD_2_NEW
WHERE
  FIELD_1_NEW = FIELD_2_NEW;
LOAD *,
  SubField(FIELD_1,chr(10)) as FIELD_1_NEW,
  SubField(FIELD_2,chr(10)) as FIELD_2_NEW;
LOAD ID,
  FIELD_1,
  FIELD_2
FROM
  [Cartesian_sample.xlsx]
  (ooxml, embedded labels, table is Sheet1);

[mkiran_18]

Hi Petter,

Thanks for your help.