topic Re: Split values in row into column in App Development

Split values in row into column

Woojtek_96 — Tue, 09 Nov 2021 14:01:33 GMT

Hi,

I have this kind of data: (Table1)

ID	Product	ProductPrice	SaleType
1	{TV, PC, PS5}	{999, 1000, 1001}	{Local, Online, Local}

and i want to achive table like: (Table2)

ID	Product	ProductPrice	SaleType
1	TV	999	Local
1	PC	1000	Online
1	PS5	1001	Local

I tried this:

Table2:
Load
ID,
SubField(Product, ',') as Product,
SubField(ProductPrice, ',') as ProductPrice,
SubField(SaleType, ',') as SaleType
Resident Table1;
Drop Table Table1;

But it create duplicate records as many as all values in table1 so i have 27 rows but i want only 3 rows

Please help ASAP

Thanks for your time

Best Regards

Woojtek

Re: Split values in row into column

Or — Wed, 10 Nov 2021 12:07:40 GMT

I'm not sure if this is exactly what you're after, but (assuming that the arrays for each row are always going to be the same size, otherwise this will break):

Temp:
Load * INLINE [
ID, Product, ProductPrice , SaleType
1, 'TV, PC, PS5', '999, 1000, 1001', 'Local, Online, Local'
2, 'TV, PC, PS5', '998, 1002, 1003', 'Local, Online, Local'
];

Load ID, Subfield(ProductPrice,',') as ProductPrice, RowNo() as Row
Resident Temp;
JOIN
Load ID, Subfield(SaleType,',') as SaleType, RowNo() as Row
Resident Temp;
JOIN
Load ID, Subfield(Product,',') as Product, RowNo() as Row
Resident Temp;

Drop Table Temp;