Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cross table with multiple value fields

Hi Gurus,

I am trying to transform an xls into a more reportable format in Qlikview, xls format is unfortunately is as below

DeptNo
DeptName
Date1
Exp1
Date2
Exp2Date3Exp3Date4Exp4Date5Exp5
1accounts20/02/20161019/02/20161018/02/20161017/02/20161016/02/201610
2marketing20/02/20162019/02/20162018/02/20162017/02/20162016/02/201620
3sales20/02/20163019/02/20163018/02/20163017/02/20163016/02/201630

I want to transform this data into below format , I tried cross table but was not able to work out how to do it easily. Please help !

DeptnoDeptNameDateExpense
1accounts20/02/201610
1accounts19/02/201610
1accounts18/02/201610
1accounts17/02/201610
1accounts16/02/201610
2marketing20/02/201620
2marketing19/02/201620
2marketing18/02/201620
2marketing17/02/201620
2marketing16/02/201620
3sales20/02/201630
3sales19/02/201630
3sales18/02/201630
3sales17/02/201630
3sales16/02/201630
4 Replies
Not applicable
Author

Hi Guys,

I have achieved this by a short script like as below

[Raw_Data]:

LOAD DeptNo,  DeptName, Date1, Exp1, Date2, Exp2, Date3, Exp3, Date4, Exp4, Date5, Exp5

FROM xls;

set a=2;

NoConcatenate

[Final]:

Load DeptNo, DeptName, Dat1 as Date, Exp1 as Expense resident [Raw_data];

Do while a<6

Concatenate (Final)

Load DeptNo, DeptName, Date$(a) as Date, Exp$(a) AS Expense

resident [Raw_Data];

Let a=a+1;

Loop

drop table [Raw_Data];

vinicyusfroes
Contributor
Contributor

Other way:

 

[Name]:
CrossTable (Order, Name)
Load
ApplyMap('Mat',[E-mail],Num#([Mat])) as [Matrícula],
[1) Name] as 1,
[2) Name] as 2,
[3) Name] as 3
From [https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/gviz/tq?sheet=Answers&tqx=ou...] (html, utf8, embedded labels);

[Born]:
CrossTable (Order, Born)
Load
ApplyMap('Mat',[E-mail],Num#([Mat])) as [Matrícula],
[1) Name] as 1,
[2) Name] as 2,
[3) Name] as 3
From [https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/gviz/tq?sheet=Answers&tqx=ou...] (html, utf8, embedded labels);

 

//Mat and Order will create automatic SynKeys (No problem!)

 

[Name and Born]:

Load
*
Resident Name;

Left Join

Load
*
Resident Born;

 

Drop Tables Name, Born;

Saravanan_Desingh

One solution is:

map:
Mapping
LOAD 'Exp'&IterNo(),'Date'&IterNo()
AutoGenerate 1
While IterNo()<=5;

tab1:
LOAD * INLINE [
    DeptNo, DeptName, Date1, Exp1, Date2, Exp2, Date3, Exp3, Date4, Exp4, Date5, Exp5
    1, accounts, 20/02/2016, 10, 19/02/2016, 11, 18/02/2016, 12, 17/02/2016, 13, 16/02/2016, 14
    2, marketing, 20/02/2016, 20, 19/02/2016, 20, 18/02/2016, 20, 17/02/2016, 20, 16/02/2016, 20
    3, sales, 20/02/2016, 30, 19/02/2016, 30, 18/02/2016, 30, 17/02/2016, 30, 16/02/2016, 30
];

tab1X:
CrossTable(Key1, Date, 2)
LOAD * Resident tab1;

tab2X:
CrossTable(Key2, Expense, 2)
LOAD * Resident tab1;

Out:
NoConcatenate
LOAD * Resident tab1X
Where Key1 Like 'Date*';
Left Join(Out)
LOAD DeptNo, DeptName, ApplyMap('map', Key2) As Key1, Expense 
Resident tab2X
Where Key2 Like 'Exp*';

Drop Table tab1, tab1X, tab2X;
Saravanan_Desingh

commQV72.PNG