Cross table with multiple value fields

[#1]

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

[#2]

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]

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]