Cross table with 2 data columns

[Anonymous]

Hi Experts,

I need your help with the below situation.

I have the below table in the DB:

Invoice	Customer Code	Item	Qty 1	Qty 2	Qty 3	Supplied 1	Supplied 2	Supplied 3
1	100	4699895	2	2	1	0	0	0
1	100	4545927	9	9	2	0	1	0
2	200	6998259	9	6	9	1	2	3

I want to get a table that holds the data in a way that each type of qty will be side by side.

i.e. in the First line Qty 1 and Supplied 1 are the same type of qty so they will be called type 1.

the Qty will be 2 and the Qty supplied will be 0 (please look on the 1st row in the table below)

Please note that i have to do it my script.

The final result should look like the below:

Invoice	Customer Code	Item	Type	Qty	Qty Supplied
1	100	4699895	1	2	0
1	100	4699895	2	2	0
1	100	4699895	3	1	0
1	100	4545927	1	9	0
1	100	4545927	2	9	1
1	100	4545927	3	2	0
2	200	6998259	1	9	1
2	200	6998259	2	6	2
2	200	6998259	3	9	3

Thanks

[sunny_talwar]

Try this

Table:

CrossTable(Field, Value, 3)

LOAD * INLINE [

    Invoice, Customer Code, Item, Qty 1, Qty 2, Qty 3, Supplied 1, Supplied 2, Supplied 3

    1, 100, 4699895, 2, 2, 1, 0, 0, 0

    1, 100, 4545927, 9, 9, 2, 0, 1, 0

    2, 200, 6998259, 9, 6, 9, 1, 2, 3

];

TempTable:

LOAD Invoice,

[Customer Code],

Item,

Field,

Value,

SubField(Field, ' ', 2) as Type

Resident Table;

DROP Table Table;

FinalTable:

LOAD Invoice,

[Customer Code],

Item,

Type,

Value as Qty

Resident TempTable

Where WildMatch(Field, 'Qty*');

Left Join (FinalTable)

LOAD Invoice,

[Customer Code],

Item,

Type,

Value as [Qty Supplied]

Resident TempTable

Where WildMatch(Field, 'Supplied*');

DROP Table TempTable;

[marcelviegas]

hi Roei Benishti,

follows qvw attached:

temp:

LOAD Invoice, [Customer Code], Item, [Qty 1], [Qty 2], [Qty 3], [Supplied 1], [Supplied 2],

     [Supplied 3]

FROM (ooxml, embedded labels, table is Plan1);

NoConcatenate

table_result:

load Invoice,[Customer Code],Item,num(1) as type,sum([Qty 1]) as qty, sum ([Supplied 1]) as [Qty Supplied]

Resident temp

group by Invoice,[Customer Code],Item;

Concatenate

load Invoice,[Customer Code],Item,num(2) as type,sum([Qty 2]) as qty, sum ([Supplied 2]) as [Qty Supplied]

Resident temp

group by Invoice,[Customer Code],Item;

Concatenate

load Invoice,[Customer Code],Item,num(3) as type,sum([Qty 3]) as qty, sum ([Supplied 3]) as [Qty Supplied]

Resident temp

group by Invoice,[Customer Code],Item;

drop table temp;