Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
1 Reply
Not applicable

Re: Cross table with multiple value fields

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

Community Browser