Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
simon_adams
New Contributor

Crosstable with two columns and two rows

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

2 Replies
Highlighted
vinieme12
Esteemed Contributor II

Re: Crosstable with two columns and two rows

Highlighted

Re: Crosstable with two columns and two rows

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;