Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Excel Sheet with Cross Table

Hello,

I need to load in QVD the datas of a Excel file.

My problem is that in this excel with have 2 Dimensions :

- the dimension Month

-The dimension Countries

and 3 Indicators BUDGET, GGR, NC

I want in the QVD for each month, for Each country these 3 indicators in the same line.

Can you help me please to do this?

Thanks a lot

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re. :Re: Excel Sheet with Cross Table

Thank your for your help,

I have do an other method with many time:


CrossTable(B, Month, 2)
LOAD
A as Country,
B as KPI,
C as Jan,
D as Feb,
E as Mar,
F as Apr,
G as May,
H as Jun,
I as Jul,
J as Aug,
K as Sep,
L as Oct,
M as Nov,
N as Dec
FROM

(ooxml, no labels, header is 1 lines, table is Sheet1);

TEMP1:
LOAD
'$(Year)' as Year,
Country,
B as Month,
Month as Budget
RESIDENT TEMP
WHERE KPI = 'Budget';

LEFT JOIN(TEMP1)
LOAD
'$(Year)' as Year,
Country,
B as Month,
Month as NewCustomers
RESIDENT TEMP
WHERE KPI = 'New cust. (NC)';

LEFT JOIN(TEMP1)
LOAD
'$(Year)' as Year,
Country,
B as Month,
Month as GGR
RESIDENT TEMP
WHERE KPI = 'GGR';

DROP TABLE TEMP;

TEMP2:
NOCONCATENATE
LOAD
Year,
Month,
DATE(Makedate(Year,
if(Month = 'Jan',01,
if(Month = 'Feb',02,
if(Month = 'Mar',03,
if(Month = 'Apr',04,
if(Month = 'May',05,
if(Month = 'Jun',06,
if(Month = 'Jul',07,
if(Month = 'Aug',08,
if(Month = 'Sep',09,
if(Month = 'Oct',10,
if(Month = 'Nov',11,
if(Month = 'Dec',12))))))))))))),'YYYY-MM-DD') as Date,
if(Country = 'Belgium Fr' OR Country = 'Belgium Nl','Belgium',
if(Country = 'Czech Rep', 'Czech Rep.',
if(Country = 'Netherlands', 'Netherland',
if(Country = 'UK & Others', 'United Kingdom',
Country )))) as Country,
Budget as Budget,
NewCustomers as NewCustomers,
GGR as GGR
RESIDENT TEMP1
WHERE Country <> 'TOTAL'
;
DROP TABLE TEMP1;

TEMP3:
NOCONCATENATE
LOAD
Year,
Month,
Date,
Country,
SUM(Budget) as Budget,
SUM(NewCustomers) as NewCustomers,
SUM(GGR) as GGR
RESIDENT TEMP2
GROUP BY Year,
Month,
Date,
Country
;
DROP TABLE TEMP2;


I think, we can compare our methods 🙂

Best regard

Bruno

2 Replies
prieper
Honored Contributor II

Excel Sheet with Cross Table

Hi,

You might have a look into the CROSSTABLE-function, script might look:

XTable:
LOAD
IF(LEN(TRIM(F1)) = 0, PEEK(F1), F1) AS F1, // ensure that each row has a country-info
F2,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM

(ooxml, embedded labels, table is Feuil1);

Data:
CROSSTABLE(Month, Value, 2) LOAD * RESIDENT XTable;
DROP TABLE XTable;
RENAME FIELD F1 TO Country;
RENAME FIELD F2 TO Item;


HTH
Peter

Not applicable

Re. :Re: Excel Sheet with Cross Table

Thank your for your help,

I have do an other method with many time:


CrossTable(B, Month, 2)
LOAD
A as Country,
B as KPI,
C as Jan,
D as Feb,
E as Mar,
F as Apr,
G as May,
H as Jun,
I as Jul,
J as Aug,
K as Sep,
L as Oct,
M as Nov,
N as Dec
FROM

(ooxml, no labels, header is 1 lines, table is Sheet1);

TEMP1:
LOAD
'$(Year)' as Year,
Country,
B as Month,
Month as Budget
RESIDENT TEMP
WHERE KPI = 'Budget';

LEFT JOIN(TEMP1)
LOAD
'$(Year)' as Year,
Country,
B as Month,
Month as NewCustomers
RESIDENT TEMP
WHERE KPI = 'New cust. (NC)';

LEFT JOIN(TEMP1)
LOAD
'$(Year)' as Year,
Country,
B as Month,
Month as GGR
RESIDENT TEMP
WHERE KPI = 'GGR';

DROP TABLE TEMP;

TEMP2:
NOCONCATENATE
LOAD
Year,
Month,
DATE(Makedate(Year,
if(Month = 'Jan',01,
if(Month = 'Feb',02,
if(Month = 'Mar',03,
if(Month = 'Apr',04,
if(Month = 'May',05,
if(Month = 'Jun',06,
if(Month = 'Jul',07,
if(Month = 'Aug',08,
if(Month = 'Sep',09,
if(Month = 'Oct',10,
if(Month = 'Nov',11,
if(Month = 'Dec',12))))))))))))),'YYYY-MM-DD') as Date,
if(Country = 'Belgium Fr' OR Country = 'Belgium Nl','Belgium',
if(Country = 'Czech Rep', 'Czech Rep.',
if(Country = 'Netherlands', 'Netherland',
if(Country = 'UK & Others', 'United Kingdom',
Country )))) as Country,
Budget as Budget,
NewCustomers as NewCustomers,
GGR as GGR
RESIDENT TEMP1
WHERE Country <> 'TOTAL'
;
DROP TABLE TEMP1;

TEMP3:
NOCONCATENATE
LOAD
Year,
Month,
Date,
Country,
SUM(Budget) as Budget,
SUM(NewCustomers) as NewCustomers,
SUM(GGR) as GGR
RESIDENT TEMP2
GROUP BY Year,
Month,
Date,
Country
;
DROP TABLE TEMP2;


I think, we can compare our methods 🙂

Best regard

Bruno