4 Replies Latest reply: May 24, 2016 3:35 AM by Abeer Bhatnagar RSS

    Loading Excel files with Row Header

    Abeer Bhatnagar

      Hi folks,

       

      I'm trying to load an excel file in Qlikview which has Customer Name in Row Headers, each file can have multiple number of customers and each customer can have any number of records. I've created a sample xlsx for my requirements, I'm not quite sure what is best way to load it in my script ?

       

      Can anyone please help ?

       

      Regards

        • Re: Loading Excel files with Row Header
          Manish Kachhia
          Input:
          LOAD RowNo() as LineNO,
            A, 
               B
          FROM
          Workbook1.xlsx
          (ooxml, explicit labels, table is Sheet1);
          
          
          TempMaxLine:
          Load Max(LineNO) as MaxLine Resident Input;
          Let vTotalLine = Num(Peek('MaxLine',0,'TempMaxLine'));
          Drop Table TempMaxLine;
          
          
          Temp:
          Load Trim(SubField(A,':',-1)) as Customer, A as OriginalCustomer Resident Input Where WildMatch(A,'*Customer*');
          
          
          TempCount:
          Load COUNT(DISTINCT Customer) as TotalCustomer Resident Temp; 
          Let vTotalCustomer = Num(Peek('TotalCustomer',0,'TempCount'));
          
          
          Drop Table TempCount;
          
          
          For i=0 to $(vTotalCustomer)-1
            Let j1 = Peek('OriginalCustomer',$(i),'Temp');
            Let j2 = Peek('OriginalCustomer',$(i)+1,'Temp');
            TempLineNO:
            Load LineNO Resident Input Where Match(A,'$(j1)','$(j2)');
            TempMinMaxLineNO:
            Load Max(LineNO) as MaxLine, Min(LineNO) as MinLine Resident TempLineNO;
            Drop Table TempLineNO;
            Let vLineNOFrom = Num(Peek('MinLine',0,'TempMinMaxLineNO'));
            Let vLineNOTo = IF(Len('$(j2)')=0,'$(vTotalLine)'+1, RangeMax(Num(Peek('MaxLine',0,'TempMinMaxLineNO'))));
            Drop Table TempMinMaxLineNO;
            OutPut:
            Load A as Date, B as value, Trim(SubField('$(j1)',':',-1)) as Customer Resident Input Where LineNO > $(vLineNOFrom)+1 and LineNO < $(vLineNOTo);
          Next i;
          
          
          Drop Tables Input, Temp;
          
          
          Let i = Null();
          Let j1 = Null();
          Let j2 = Null();
          Let vLineNOFrom = Null();
          Let vLineNOTo = Null();
          Let vTotalCustomer = Null();
          
          • Re: Loading Excel files with Row Header
            kushal chawda

            If you have more than two columns then add add that in mapping tables.

             

            RenameFields:
            mapping LOAD * Inline [
            RenameFrom, RenameTo
            A, Col 1
            B, Col 2 ]
            ;

            Data:
            LOAD RecNo() as Rec,
            if(WildMatch(lower(trim(A)),'customer*'), SubField(A,':',2)) as Customer,
            *

            FROM
            [Workbook1.xlsx]
            (
            ooxml, no labels, table is Sheet1);

            New:
            LOAD *,
            if(WildMatch(lower(trim(A)),'customer*') or WildMatch(lower(trim(A)),'col*') ,1,0) as Flag;
            LOAD *,
            if(len(trim(Customer))=0,Peek('CustomerName'),Customer) as CustomerName
            Resident Data
            Order by Rec asc;

            DROP Table Data;

            DROP Fields Customer;


            Final:
            NoConcatenate
            LOAD *
            Resident New
            where Flag=0;

            DROP Table New;

            DROP Field Flag;

            RENAME Fields using RenameFields;

             

            • Re: Loading Excel files with Row Header
              Tamil Nagaraj

              Abeer,

               

              Try,

               

              Temp:
              LOAD Date(@1) as Col1, 
                   Num(@2) as Col2, 
                   Replace(@3,'Customer: ','') as Customer
              FROM
              Workbook1.xlsx
              (ooxml, no labels, table is Sheet1, filters(
              ColXtr(1, RowCnd(CellValue, 1, StrCnd(contain, 'Customer')), 0),
              Replace(3, top, StrCnd(null))
              ));
              
              NoConcatenate
              Data:
              Load *
              Resident Temp where Len(Trim(Col1))>0;
              
              DROP Table Temp;
              

               

              Untitled.png

              Sample QVW file attached for your data.

              • Re: Loading Excel files with Row Header
                Abeer Bhatnagar

                Thanks all for the replies, though i'm sure all the solutions are correct, I used kushal's code to achieve my purpose