Excel Sheet with Cross Table

[#1]

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

[prieper]

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

[#3 · Accepted solution]

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