Undo Crosstable

[Pedro_Rodriguez]

Hi everyone,

I start from a table like this:

load * INLINE [

Type, Pck, Provid

1, pck11, prv111

1, pck11, prv112

1, pck12, prv122

2, pck21, prv211

3, pck31, prv311

3, pck31, prv312

3, pck31, prv313

];

What I want to get it's something like:

Type	Pck	Provid1	Provid2	Provid3
1	pck11	prv111	prv112
1	pck12	prv122
2	pck21	prv211
3	pck31	prv311	prv312	prv313

Basically is creating a record per each pair Type-Pck and populate the remainig columns with its providers

Bear in mind that the maximun number of providers is not fixed.

Can you guys help me?

Thanks a lot in advance

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD *,

AutoNumber(RowNo(), Pck) as Number;

LOAD * INLINE [

    Type, Pck, Provid

    1, pck11, prv111

    1, pck11, prv112

    1, pck12, prv122

    2, pck21, prv211

    3, pck31, prv311

    3, pck31, prv312

    3, pck31, prv313

];

FinalTable:

LOAD DISTINCT Type,

Pck

Resident Table;

FOR i = 1 to FieldValueCount('Number')

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

Left Join (FinalTable)

LOAD DISTINCT Type,

Pck,

Provid as [Provid$(vField)]

Resident Table

Where Number = $(vField);

NEXT

DROP Table Table;

[Pedro_Rodriguez]

Thanks a lot Sunny! Works perfectly