Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
I have one Excel with two header rows, I want to use crosstable to load into QlikView.
The attachment is the source file, and the 'target' sheet is what I want.
Does someone could guide me how to achieve it.
Thx a lot.
Check for empty rows e.g if Type (Column A) has text (see red below)
TMP3:
LOAD A as Type,
applymap('MAP1',1) as Category,
B,
C,
D,
E,
F,
G
FROM
[16B Finance.xlsx]
(ooxml, no labels, table is '16B')
where recno() >2 and Len(A)>0;
Hi Devarasu R
I tried Stefan solution. However, after loding into QlikView, from table viewer, there's one row should not be existed.
I marked in Red, do you know what's wrong with my script, thx.
Check for empty rows e.g if Type (Column A) has text (see red below)
TMP3:
LOAD A as Type,
applymap('MAP1',1) as Category,
B,
C,
D,
E,
F,
G
FROM
[16B Finance.xlsx]
(ooxml, no labels, table is '16B')
where recno() >2 and Len(A)>0;
Hi Ran,
PFA, solution.
Change the numeric value of date as required.
Regards,
Pankaj
Hi Xu,
We can do this in two steps. A transposition then crosstable.
Transpose:
LOAD F1 as Category,
Date(Type,'YY/MM') as Date,
[Application Biz],
[DIRECT Biz],
[Direct expense]
FROM
test.xlsx
(ooxml, embedded labels, table is test, filters(
Transpose()
));
CrossTable:
CrossTable(Type,Value,2) LOAD * Resident Transpose Where not IsNull(Date);
DROP Table Transpose;
gives this:
Type | Date | Value | Category |
---|---|---|---|
Application Biz | 16/10 | 0.00 | Budget |
Application Biz | 16/11 | 773.96 | Budget |
Application Biz | 16/12 | 2,268.69 | Budget |
Application Biz | 17/01 | 773.96 | Budget |
Application Biz | 17/02 | 0.00 | Evaluate |
Application Biz | 17/03 | 3,307.91 | Evaluate |
DIRECT Biz | 16/10 | 0.00 | Budget |
DIRECT Biz | 16/11 | 0.00 | Budget |
DIRECT Biz | 16/12 | 903.14 | Budget |
DIRECT Biz | 17/01 | 0.00 | Budget |
DIRECT Biz | 17/02 | 0.00 | Evaluate |
DIRECT Biz | 17/03 | 903.14 | Evaluate |
Direct expense | 16/10 | 36.00 | Budget |
Direct expense | 16/11 | 45.00 | Budget |
Direct expense | 16/12 | 41.00 | Budget |
Direct expense | 17/01 | 31.00 | Budget |
Direct expense | 17/02 | 26.00 | Evaluate |
Direct expense | 17/03 | 40.00 | Evaluate |
Regards
Andrew
Hi Piet
The table is right now. Thx!
Hi Andrew
Seems your solution is better, no need to map anything.