Load Rows as Columns while importing Excel Report

[#1]

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

[Jason_Michaelides]

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?

[#3]

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

[#4 · Accepted solution]

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;

[#5]

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.

[#6]

add :

drop table robo2;

at the end of script

[#7]

Thank you so much. That's perfect solution