topic Re: Load Rows as Columns while importing Excel Report in QlikView

Load Rows as Columns while importing Excel Report

Fri, 17 Aug 2012 03:59:04 GMT

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

Re: Load Rows as Columns while importing Excel Report

Jason_Michaelides — Fri, 17 Aug 2012 07:18:27 GMT

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?

Re: Load Rows as Columns while importing Excel Report

Fri, 17 Aug 2012 07:53:26 GMT

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

Re: Load Rows as Columns while importing Excel Report

Fri, 17 Aug 2012 08:00:33 GMT

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;

Re: Load Rows as Columns while importing Excel Report

Fri, 17 Aug 2012 08:48:31 GMT

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.

Re: Load Rows as Columns while importing Excel Report

Fri, 17 Aug 2012 08:53:26 GMT

add :

drop table robo2;

at the end of script

Re: Load Rows as Columns while importing Excel Report

Fri, 17 Aug 2012 09:01:51 GMT

Thank you so much. That's perfect solution