Type-Value pairs to separate rows

Naule — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Type-Value pairs to separate rows

Taoufiq_Zarra — Wed, 09 Sep 2020 11:43:28 GMT

@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:

topic Type-Value pairs to separate rows in QlikView

Type-Value pairs to separate rows

Re: Type-Value pairs to separate rows