Type-Value pairs to separate rows

[Naule]

Hi,

I've got source data that reduced would look something alikeL

Customer	Shipment	Type	Value	Type	Value	Type	Value	Type	Value	Type	Value	Type	Value
354576158	13577750715	A222JR	472	D438HFJ	-7,08	F390OFJ	14,23	G155OFKJI	100,62
354576158	13577104016	A222JR	115,4	D438HFJ	-74,41	F390OFJ	7,38
354576158	13577451207	B254DF	10,32	E281FM	62,37
354576158	13577422549	A222JR	10428,6	D438HFJ	-1764,84	F390OFJ	1429,52
354576158	13577422549	C364AV	4,52	B254DF	9,95	E281FM	62,73
354576158	13577353198	D204DG	1,4	A222JR	40,4	D438HFJ	-32,16	F390OFJ	0,18	G155OFKJI	2,06
354576158	13577567321	D204DG	1,4	A222JR	76,2	D438HFJ	-62,41	F390OFJ	0,3	G155OFKJI	4,83	G151RAU	7,5
354576158	13577402709	D204DG	51,4	A222JR	514	D438HFJ	-7,71	F390OFJ	11,19	G155OFKJI	151,8	H132VN	154

In the script, we would like to transpose this to separate rows for the type / value pairs:

Customer	Shipment	Type	Value
354576158	13577750715	A222JR	472
354576158	13577750715	D438HFJ	-7,08
354576158	13577750715	F390OFJ	14,23
354576158	13577750715	G155OFKJI	100,62
354576158	13577104016	A222JR	115,4
354576158	13577104016	D438HFJ	-74,41
354576158	13577104016	F390OFJ	7,38
354576158	13577451207	B254DF	10,32
354576158	13577451207	E281FM	62,37
354576158	13577422549	A222JR	10428,6
354576158	13577422549	C364AV	4,52
354576158	13577422549	D438HFJ	-1764,84
354576158	13577422549	B254DF	9,95
354576158	13577422549	F390OFJ	1429,52
354576158	13577422549	E281FM	62,73
354576158	13577353198	D204DG	1,4
354576158	13577353198	A222JR	40,4
354576158	13577353198	D438HFJ	-32,16
354576158	13577353198	F390OFJ	0,18
354576158	13577353198	G155OFKJI	2,06
354576158	13577567321	D204DG	1,4
354576158	13577567321	A222JR	76,2
354576158	13577567321	D438HFJ	-62,41
354576158	13577567321	F390OFJ	0,3
354576158	13577567321	G155OFKJI	4,83
354576158	13577567321	G151RAU	7,5
354576158	13577402709	D204DG	51,4
354576158	13577402709	A222JR	514
354576158	13577402709	D438HFJ	-7,71
354576158	13577402709	F390OFJ	11,19
354576158	13577402709	G155OFKJI	151,8
354576158	13577402709	H132VN	154

The original source data are CSV exports with varying numbers of pairs, ranging from 2-3 pairs to 134. As of such I would prefer not to subsequently hard load every column pair. 

What's the easiest way to achieve this? 

[Taoufiq_Zarra · Accepted solution]

@Naule 

One solution is :

Input:

CrossTable(Fields, Data, 2)

LOAD * FROM
.\Classeur1.xlsx
(ooxml, embedded labels, table is Feuil1);


Tmp:
load *,if(wildmatch(Fields,'Value*')>0,Data,'99999') as Value_,if(wildmatch(Fields,'Type*')>0,Data,'99999') as Type_,if(rowno()=1,0,if(keepchar(Fields,'0123456789')=previous(keepchar(Fields,'0123456789')),peek(n),peek(n)+1)) as n resident Input;


drop table Input;



Final:


load Customer,Shipment,n,Type_ as Type resident Tmp where Type_<>'99999';

join

load Customer,Shipment,n,Value_ as Value resident Tmp  where Value_<>'99999';

drop table Tmp;
drop fields n;

Input:Classeur.xlsx

output: