Crosstable with two columns and two rows

[Anonymous]

Suck trying to pull the attached file into a flat cross file so i can report in Qlik Sense... help??

I want to report on business unit / KPI / vs budget / actual / year....

[vinieme12]

See this post

https://community.qlik.com/mobile/mobile-access.jspa#jive-content?content=/api/core/v3/contents/2178...

[Kushal_Chawda]

Data:
LOAD F1,
     F2,
     January,
     January1,
     January2,
     February,
     February1,
     February2,
     March,
     March1,
     March2,
     April,
     April1,
     April2
FROM
[Cross table 2.xlsx]
(ooxml, embedded labels, table is Sheet1);


t1:
CrossTable(Name,Fact,2)
LOAD *
Resident Data;

DROP Table Data;

Left Join (t1)
LOAD Name,
          Fact as Name2
Resident t1
where F2='KPI';

t2:
LOAD F1 as [Business Unit],
           F2 as KPI,
           Fact,
           Name2&'-'&Name as Name
Resident t1
where F2<>'KPI';

DROP Table t1;


t3:
LOAD  left(Name,4) as Year,
           month(date#(purgechar(SubField(Name,'-',2),'0123456789'),'MMMM')) as Month,
           [Business Unit],
           if(WildMatch(lower(Name),'*actual*'),'Actual',
           if(WildMatch(lower(Name),'*budget*'),'Budget')) as Type,
           KPI,
           Fact
Resident t2 ;

DROP Table t2;

Final:
LOAD Year,
          Month,
          [Business Unit],
          KPI,
          sum(if(Type='Actual',Fact)) as Actual,
          sum(if(Type='Budget',Fact)) as Budget
Resident t3
Group by Year,
          Month,
          [Business Unit],
          KPI;

DROP Table t3;