topic Re: Cross table with multiple value fields in QlikView

Cross table with multiple value fields

Tue, 23 Feb 2016 13:23:01 GMT

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	Exp2	Date3	Exp3	Date4	Exp4	Date5	Exp5
1	accounts	20/02/2016	10	19/02/2016	10	18/02/2016	10	17/02/2016	10	16/02/2016	10
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

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 !

Deptno	DeptName	Date	Expense
1	accounts	20/02/2016	10
1	accounts	19/02/2016	10
1	accounts	18/02/2016	10
1	accounts	17/02/2016	10
1	accounts	16/02/2016	10
2	marketing	20/02/2016	20
2	marketing	19/02/2016	20
2	marketing	18/02/2016	20
2	marketing	17/02/2016	20
2	marketing	16/02/2016	20
3	sales	20/02/2016	30
3	sales	19/02/2016	30
3	sales	18/02/2016	30
3	sales	17/02/2016	30
3	sales	16/02/2016	30

Re: Cross table with multiple value fields

Tue, 23 Feb 2016 14:48:19 GMT

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];

Re: Cross table with multiple value fields

vinicyusfroes — Thu, 19 Sep 2019 20:02:00 GMT

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=out:html] (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=out:html] (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;

Re: Cross table with multiple value fields

Saravanan_Desingh — Sun, 24 May 2020 23:56:04 GMT

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;

Re: Cross table with multiple value fields

Saravanan_Desingh — Sun, 24 May 2020 23:57:28 GMT