2 Replies Latest reply: Dec 8, 2014 8:06 PM by Marco Wedel RSS

    Excel import, bit more complicated

      Hello,

       

      I have attached example file (Budget_exampledata) where sales person have added budget for year 2015 per month (B2015) which I would like to import. The question is now that that is has excess data and also normal cross table doesn't fit as it has two rows as qualifiers or how to say..

       

      First rows as example:

      MonthNum112233445566778899101011111212
      ProductSalespersonZoneTOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015TOT2014B2015
      FREEZERKIAAPE777E54E0E0E0E00E07540E0003210432

       

      And the result should be (colors are just to give the hint what data is what):

      ProductSalespersonZoneDatatypeDateAmount
      FREEZERKIAAPBudget1.1.2015777
      FREEZERKIAAPBudget1.2.201554
      FREEZERKIAAPBudget1.8.2015754
      FREEZERKIAAPBudget1.11.2015321
      FREEZERKIAAPBudget1.12.2015432

       

      So the TOT2014 rows are not needed. As they are in the file just to give the hint for sales person for his totals previous years. Also the year (in yeallow) is not mandatory to get from the file, that can be done manually.

       

       

      -Juha

        • Re: Excel import, bit more complicated
          Alan Hendrickx

          Hi Juha,

           

          Interesting load! What I would suggest is a cross table to initially and then a resident load to apply the new field and also handle the final formatting. I have tested the below script on your sample data and it works.

           

          A

           

          InitialLoad:

          CrossTable(Month, Data, 3)

          LOAD F1 as Product,

               F2 as Salesperson,

               MonthNum,

               [11] as [01/01/2015],

               [21] as [01/02/2015],

               [31] as [01/03/2015],

               [41] as [01/04/2015],

               [51] as [01/05/2015],

               [61] as [01/06/2015],

               [71] as [01/07/2015],

               [81] as [01/08/2015],

               [91] as [01/09/2015],

               [101] as [01/10/2015],

               [112] as [01/11/2015],

               [121] as [01/12/2015]

          FROM

          [..\Downloads\Budget_exampledata.xlsx]

          (ooxml, embedded labels, table is Budget);

           

           

          FormattedTable:

          LOAD

            Product,

            Salesperson,

            MonthNum AS Zone,

            'Budget' AS Datatype,

            Month,

            Data AS Amount

          Resident InitialLoad

          Where Product<>'Product'

          ;

           

           

          Drop Table InitialLoad;

          • Re: Excel import, bit more complicated
            Marco Wedel

            Hi,

             

            one slightly more generic approach without hard coded field names could be:

             

            QlikCommunity_Thread_144881_Pic1.jpg

             

            tabSalesBudget:
            CrossTable(ColNam, Amount, 3)
            LOAD *
            FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]
            (ooxml, no labels, header is 2 lines, table is Budget, filters(Transpose(),Transpose()))
            Where RecNo()<=16;
            
            mapColNam:
            mapping LOAD
              '@'&(RecNo()+3),
              MakeDate(Right(@2,4),@1)
            FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]
            (ooxml, no labels, table is Budget, filters(Transpose(),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1))))
            Where @2 like 'B*';
            
            Right Join (tabSalesBudget)
            LOAD Distinct
              ColNam,
              Date(ApplyMap('mapColNam', ColNam)) as Date
            Resident tabSalesBudget
            Where ApplyMap('mapColNam', ColNam);
            
            DROP Field ColNam;
            
            tabTemp:
            CrossTable(ColNum, ColNam2)
            LOAD 1, *
            FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]
            (ooxml, no labels, header is 1 lines, table is Budget, filters(Transpose(),Transpose()))
            Where RecNo()=1;
            
            mapColNam2:
            Mapping LOAD ColNum, ColNam2 Resident tabTemp;
            
            DROP Table tabTemp;
            
            RENAME Fields using mapColNam2;
            
            
            

             

            Hope this helps

             

            Regards

             

            Marco