2 Replies Latest reply: May 22, 2017 12:11 AM by kushal chawda RSS

    Crosstable with two columns and two rows

    Simon Adams

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

        • Re: Crosstable with two columns and two rows
          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;