Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Load Rows as Columns while importing Excel Report

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 ...

DateJanFebMarAprMayJunJulAugSepOctNovDecfull year
Product Atarget1.01.01.01.01.01.01.01.01.01.01.01.012.0
actuals, f/castChina1.51.51.51.51.51.51.51.51.51.51.51.518.0
France0.0
UK0.0
USA0.0
cum. dev.-0.5-0.5-0.5-0.5-0.5-0.5
Product Btarget2.02.02.02.02.02.02.02.02.02.02.02.024.0
actuals, f/castChina1.51.51.51.51.51.51.51.51.51.51.51.518.0
France0.0
UK0.0
USA1.21.21.21.21.21.21.21.21.21.21.21.214.4
cum. dev.0.00.00.00.00.80.80.8
Product Ctarget3.03.03.03.03.03.03.03.03.03.03.03.036.0
actuals, f/castChina0.0
France1.01.01.01.01.01.01.01.01.01.01.01.012.0
UK0.0
USA1.11.11.11.11.11.11.11.11.11.11.11.113.4
cum. dev.0.00.00.00.00.90.90.9
1 Solution

Accepted Solutions
Not applicable

Re: Load Rows as Columns while importing Excel Report

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 (ooxml, embedded labels, table is Sheet1);

//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 (ooxml, embedded labels, table is Sheet1) where isnull(F1)=0;

left join load distinct F3 as Country

FROM (ooxml, embedded labels, table is Sheet1) where isnull(F3)=0;

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

6 Replies
jason_michaelid
Honored Contributor II

Re: Load Rows as Columns while importing Excel Report

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?

Not applicable

Re: Load Rows as Columns while importing Excel Report

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..

Not applicable

Re: Load Rows as Columns while importing Excel Report

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 (ooxml, embedded labels, table is Sheet1);

//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 (ooxml, embedded labels, table is Sheet1) where isnull(F1)=0;

left join load distinct F3 as Country

FROM (ooxml, embedded labels, table is Sheet1) where isnull(F3)=0;

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

Not applicable

Re: Load Rows as Columns while importing Excel Report

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.

Not applicable

Re: Load Rows as Columns while importing Excel Report

add :

drop table robo2;

at the end of script

Not applicable

Re: Load Rows as Columns while importing Excel Report

Thank you so much. That's perfect solution

Community Browser