Redesign excel data

[agni_gold]

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.

[antoniotiman]

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

[agni_gold]

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.

[antoniotiman]

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