Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Alll,
I am importing a data source from Excel spreadsheet where I have columns and rows as below. I want to convert coloured rows into separate columns (please see attcahed sheet) and load into a table box. I tried to load in the script using Crosstable but it wouln't display columns as I want e.g. Product, Date, Country, Target, Actuals,f/cast and cum.dev.
I have also attached the excel sheet of what i'm trying to say.
PLEASE HELP ME ...
Date | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | full year | ||
Product A | target | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 12.0 | |
actuals, f/cast | China | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 18.0 | |
France | 0.0 | ||||||||||||||
UK | 0.0 | ||||||||||||||
USA | 0.0 | ||||||||||||||
cum. dev. | -0.5 | -0.5 | -0.5 | -0.5 | -0.5 | -0.5 | |||||||||
Product B | target | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 2.0 | 24.0 | |
actuals, f/cast | China | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 1.5 | 18.0 | |
France | 0.0 | ||||||||||||||
UK | 0.0 | ||||||||||||||
USA | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 1.2 | 14.4 | ||
cum. dev. | 0.0 | 0.0 | 0.0 | 0.0 | 0.8 | 0.8 | 0.8 | ||||||||
Product C | target | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 3.0 | 36.0 | |
actuals, f/cast | China | 0.0 | |||||||||||||
France | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 12.0 | ||
UK | 0.0 | ||||||||||||||
USA | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 1.1 | 13.4 | ||
cum. dev. | 0.0 | 0.0 | 0.0 | 0.0 | 0.9 | 0.9 | 0.9 |
try this code
//first load - duplicate missing dimension values (product and country)
robo1:
LOAD
if(isnull(F1),peek(tempProduct),F1) as tempProduct,
if(isnull(Date),peek(tempType),Date) as tempType,
F3 as tempCountry,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
FROM
//crossinf prev loaded table
robo2:
crosstable (month, value, 3)
load tempProduct as product,
tempType as type,
tempCountry as country,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
resident robo1;
drop table robo1;
//creating final table (each country and product)
final:
load F1 as Product
FROM
left join load distinct F3 as Country
FROM
//adding target column
left join (final) load product as Product, type, month as Month, value as Target resident robo2 where type='target';
drop field type from final;
//adding cum. dev. column
left join (final) load product as Product, type, month as Month, value as [cum. dev.] resident robo2 where type='cum. dev.';
drop field type from final;
//adding actuals, f/cast column
left join (final) load product as Product, country as Country, type, month as Month, value as [actuals, f/cast] resident robo2 where type='actuals, f/cast';
drop field type from final;
It looks like CrossTable() and a pivot table should achieve this no problem. Can you provide a spreadsheet of sample data as its not clear from the table above?
Thanks for reply Jason. Please see attachments 'Excel_Report_in_QlikView.xlsx'. I have also attcahed qvw file.
Using CrossTable() I loaded file as;
MyTable:
CrossTable(Date, Data)
LOAD F1 as Products,
Date,
F3,
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec,
[full year]
FROM
Excel_Report_in_QlikView.xlsx
(ooxml, embedded labels, table is Sheet1);
I want Country, Targets and Data in separate columns..
Really appreciate your help guys..
try this code
//first load - duplicate missing dimension values (product and country)
robo1:
LOAD
if(isnull(F1),peek(tempProduct),F1) as tempProduct,
if(isnull(Date),peek(tempType),Date) as tempType,
F3 as tempCountry,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
FROM
//crossinf prev loaded table
robo2:
crosstable (month, value, 3)
load tempProduct as product,
tempType as type,
tempCountry as country,
Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
resident robo1;
drop table robo1;
//creating final table (each country and product)
final:
load F1 as Product
FROM
left join load distinct F3 as Country
FROM
//adding target column
left join (final) load product as Product, type, month as Month, value as Target resident robo2 where type='target';
drop field type from final;
//adding cum. dev. column
left join (final) load product as Product, type, month as Month, value as [cum. dev.] resident robo2 where type='cum. dev.';
drop field type from final;
//adding actuals, f/cast column
left join (final) load product as Product, country as Country, type, month as Month, value as [actuals, f/cast] resident robo2 where type='actuals, f/cast';
drop field type from final;
Thank You Pari Pari ! I tried your codes and it is quite close to what I was looking for.. What I get now data in right columns but it returing values from both resident and drop tables and repeating it. How do I solve that problem. Please see attached file called 'MyFirstTest(2).qvw'.
Thanks again.
add :
drop table robo2;
at the end of script
Thank you so much. That's perfect solution