Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Naule
Contributor II
Contributor II

Type-Value pairs to separate rows

Hi,

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

CustomerShipmentType ValueType ValueType ValueType ValueType ValueType Value
35457615813577750715A222JR472D438HFJ-7,08F390OFJ14,23G155OFKJI100,62    
35457615813577104016A222JR115,4D438HFJ-74,41F390OFJ7,38      
35457615813577451207B254DF10,32E281FM62,37        
35457615813577422549A222JR10428,6D438HFJ-1764,84F390OFJ1429,52      
35457615813577422549C364AV4,52B254DF9,95E281FM62,73      
35457615813577353198D204DG1,4A222JR40,4D438HFJ-32,16F390OFJ0,18G155OFKJI2,06  
35457615813577567321D204DG1,4A222JR76,2D438HFJ-62,41F390OFJ0,3G155OFKJI4,83G151RAU7,5
35457615813577402709D204DG51,4A222JR514D438HFJ-7,71F390OFJ11,19G155OFKJI151,8H132VN154

 

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

CustomerShipmentType Value
35457615813577750715A222JR472
35457615813577750715D438HFJ-7,08
35457615813577750715F390OFJ14,23
35457615813577750715G155OFKJI100,62
35457615813577104016A222JR115,4
35457615813577104016D438HFJ-74,41
35457615813577104016F390OFJ7,38
35457615813577451207B254DF10,32
35457615813577451207E281FM62,37
35457615813577422549A222JR10428,6
35457615813577422549C364AV4,52
35457615813577422549D438HFJ-1764,84
35457615813577422549B254DF9,95
35457615813577422549F390OFJ1429,52
35457615813577422549E281FM62,73
35457615813577353198D204DG1,4
35457615813577353198A222JR40,4
35457615813577353198D438HFJ-32,16
35457615813577353198F390OFJ0,18
35457615813577353198G155OFKJI2,06
35457615813577567321D204DG1,4
35457615813577567321A222JR76,2
35457615813577567321D438HFJ-62,41
35457615813577567321F390OFJ0,3
35457615813577567321G155OFKJI4,83
35457615813577567321G151RAU7,5
35457615813577402709D204DG51,4
35457615813577402709A222JR514
35457615813577402709D438HFJ-7,71
35457615813577402709F390OFJ11,19
35457615813577402709G155OFKJI151,8
35457615813577402709H132VN154

 


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? 

1 Solution

Accepted Solutions
Taoufiq_Zarra

@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

Capture.PNG

 

output:

Capture.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

1 Reply
Taoufiq_Zarra

@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

Capture.PNG

 

output:

Capture.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉