topic Re: Redesign excel data in QlikView

Redesign excel data

agni_gold — Mon, 29 May 2017 15:28:47 GMT

Hi Friends,

I need one help to redesign excel source data , attached in output for (sheet name : output in attached excel)

I have tried , cross table , generic load these option but not able to create , any one can help here.

Re: Redesign excel data

micheledenardi — Mon, 29 May 2017 15:42:28 GMT

CrossTable(Period,Values,3)

BaseTab_tmp:

LOAD A,

[2016 - Sales],

[2017 - Sales],

[2016 - Budget],

[2017 - Budget]

FROM

[Sample Data.xlsx] (ooxml, embedded labels, table is Source);

BaseTab:

NoConcatenate

Load *,

left(Period,4) as Year,

mid(Period,index(Period,'-')+2,len(Period))as Type

resident BaseTab_tmp;

Re: Redesign excel data

agni_gold — Mon, 29 May 2017 15:44:47 GMT

type should be different columns, like sales and budget

Re: Redesign excel data

micheledenardi — Mon, 29 May 2017 15:56:19 GMT

See attached qvw

Re: Redesign excel data

agni_gold — Mon, 29 May 2017 15:58:45 GMT

if u see my attched excel , output require Sales and budget as column

Re: Redesign excel data

Peter_Cammaert — Mon, 29 May 2017 16:01:17 GMT

Two possible solutions:

serialize the data and put them in a Pivot table - this is the simplest technique, script-wise
go-on and create straight table data in your script using a LOAD and a JOIN

See attachment.

BTW It would be better to load data from the same source as the Excel instead of trying to twist the Excel aggregation back into something that looked like a source table and then doing the exact same thing again in QlikView. Would save you a lot of headaches in the future...

Re: Redesign excel data

agni_gold — Mon, 29 May 2017 16:03:56 GMT

Thanks , but client data is specified in source format , i need this output format in script level , as per requirement from client .

Re: Redesign excel data

Peter_Cammaert — Mon, 29 May 2017 16:09:23 GMT

Don't bother. If you run the script to the end, you'll get your required internal table. See sheet 'Complex' for a straight table showing the target data set. If you want to get that one back into Excel, export it using the XLS icon at the right of the caption or open the menu.

Re: Redesign excel data

micheledenardi — Mon, 29 May 2017 16:12:56 GMT

See attached qvw

Re: Redesign excel data

agni_gold — Mon, 29 May 2017 16:17:27 GMT

Sorry Peter , i just saw chart , not script.

Thanks for your great help.

Re: Redesign excel data

antoniotiman — Mon, 29 May 2017 16:17:46 GMT

Hi,

Temp:
CrossTable(Field, Value, 3)
LOAD A,
B,
C,
[2016 - Sales],
[2017 - Sales],
[2016 - Budget],
[2017 - Budget]
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1277464-280613/Sample%20Data.xlsx"
(ooxml, embedded labels, table is Source);
Temp1:
NoConcatenate
LOAD A,B,C,SubField(Field,'-',1) as Year,SubField(Field,'-',2) as Field,Value
Resident Temp;
Drop Table Temp;
Temp2:
Generic LOAD *
Resident Temp1;
Drop Table Temp1;
Left Join ([Temp2. Sales])
LOAD * Resident [Temp2. Budget];
Drop Table [Temp2. Budget];

Regards,

Antonio

Re: Redesign excel data

agni_gold — Mon, 29 May 2017 17:05:27 GMT

If any of the row have null value in any column like A or B or C , then this solution is not working , means left join is not happening correctly , it is giving null value in table.

Re: Redesign excel data

antoniotiman — Mon, 29 May 2017 18:34:07 GMT

Temp:
CrossTable(Field, Value, 3)
LOAD A,
B,
C,
[2016 - Sales],
[2017 - Sales],
[2016 - Budget],
[2017 - Budget]
FROM

(ooxml, embedded labels, table is Source);
Temp1:
NoConcatenate
LOAD A,B,C,Trim(SubField(Field,'-',1)) as Year,Trim(SubField(Field,'-',-1)) as Field,Value
Resident Temp;
Drop Table Temp;
Temp2:
Generic LOAD *
Resident Temp1;
Drop Table Temp1;
LOAD *,AutoNumber(A&B&C&Year) as A1 Resident Temp2.Budget;
LOAD *,AutoNumber(A&B&C&Year) as A1 Resident Temp2.Sales;
Drop Tables Temp2.Budget,Temp2.Sales;
Left Join ([Temp2.Sales-1])
LOAD A1,Budget Resident [Temp2.Budget-1];
Drop Table [Temp2.Budget-1];