Delimited Data

[kumar2]

Hi Team,

I have data in the below format, i want to create two columns with spited values for serial number and Price associated 

and in the input these are given in two columns with the delimited as "," when i am trying to achieve this using the below expression it is not giving me the exact output, Can you please help me how to achieve the same.

Attached the excel file with sample input and ouput

Final:
NoConcatenate
Load
Distinct
TRANSACTION_ID &'|'&RowNo() as TRANSACTION_ID,
SubField(SERIAL_NUMBER,',') as SERIAL_NUMBER
Resident Test;

left join(Final)
Load
TRANSACTION_ID &'|'&RowNo() as TRANSACTION_ID,
SubField(PRICE,',') as PRICE
Resident Test;

Drop Table Test;

Thanks,

Kumar

[Or]

Typically the solution would be to use a second load with Subfield(), but in your case, I'm not sure if that'll work, since the delimited number of values isn't consistent across the two columns. When this happens, you also seem to expect the values to be assigned in non-consecutive order (for transaction 13793623116, you assign 15 to the first row, 16 to the last row, and null to the middle two rows).  If you can get your values to be consistent length (for transaction 13793623116 there would be four values, even if two are blank, so {15, , ,16} then the below code should work:

Load Transaction_ID, Serial_Number,Subfield(Price,',') as Price;
LOAD
[Transaction_ID],
Subfield([Serial_Number],',') as Serial_Number,
[Price] as Price
FROM [lib://AttachedFiles/Sample.xlsx]
(ooxml, embedded labels, table is Input);

However I can't think of any solution that would somehow be able to associate the 16 value with the fourth serial number while skipping over the second and third ones.

[kumar2]

Thanks for the response

Yaa, for the last I'd data is coming as expected

But users are providing data in this format

[Or]

You could try looping through the values for each transaction ID, and pulling the relevant value using e.g. Subfield([Serial_Number,',',IterNo()) as Serial Number, Subfield([Price,',',IterNo())  as Price

But even then, this won't associate the 16 with the fourth value since there's no way to know that the middle two should be skipped over. It's just not information contained within the data.