topic Re: how to convert to cross table ? in App Development

how to convert to cross table ?

gauravgg — Tue, 20 Dec 2016 04:20:00 GMT

I have the following table

i want to convert the "Original" table to "Output" table in the script .

Original Table

ID	Category	SubCategory	type
1	A	a1,a2,a3	Z
2	B	b1,b2,b3	N
3	C	c1,c2	Q
4	A	a1,a2	X

Output Table

can anyone help me

Thanks in Advance

Gysbert_Wassenaar — Tue, 20 Dec 2016 08:36:29 GMT

Sounds like a bad idea to me, but if you insist you can try The Generic Load

sunny_talwar — Tue, 20 Dec 2016 13:05:51 GMT

I agree with gwassenaar‌, but here is another solution

Table:

LOAD * INLINE [

ID, Category, SubCategory, type

1, A, "a1,a2,a3", Z

2, B, "b1,b2,b3", N

3, C, "c1,c2", Q

4, A, "a1,a2", X

];

FinalTable:

LOAD ID,

type

Resident Table;

FOR i = 1 to FieldValueCount('Category')

LET vField = FieldValue('Category', $(i));

Left Join (FinalTable)

LOAD ID,

type,

SubCategory as [$(vField)]

Resident Table

Where Category = '$(vField)';

NEXT

DROP Table Table;