4 Replies Latest reply: Nov 17, 2016 1:57 PM by Ron Payne RSS

    Loop Help Please

    Ron Payne

      Hi,

       

      I'm simply trying to load in these multiple Excel sheets into one QVD. The setup is a more manual approach without having a macro to scan the directory before hand. No big deal as this is a one-time load. I'm struggling with the loop part. Please advise on how i can make this work. It won't heed the "do while" piece and keeps trying to loop on into infite. I'd like to try to make the intial load be a table named Historicals and concatenate the follow-up loads onto that (set vConcat). Any input on that part would be appreciated too:

       

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------

       

       

      let i = 24;

       

       

      do while $(i) <= 52

       

       

       

      //set vConcat = ;

       

       

      //$(vConcat)

      Historicals:

      LOAD

           Mid(SubField([Labor Account], '/', 3), 4, 6) AS [Cost Center],

           [Pay Code],

           Money,

           Hours,

           Wages,

           Days,

           $(i) as FiscalWeek

           //date($(vRipDate)) as CalendarDate

      FROM

      [..\..\Data\FlatFiles\Historicals\2015 ADP Week $(i).xls]   //$(i)

      (biff, embedded labels, header is 9 lines, table is Sheet1$)

      Where not IsNull([Labor Account])  //<> '' to get rid of blank cost centers?  //not isnull() is probably doing nothing

      ;

       

       

      Let i=$(i)+1;

       

       

      //set vConcat = Concatenate;

       

       

      LOOP;

       

       

       

      //STORE * from Historicals into ..\..\Data\QVDs\ADP_Data_2015.QVD (qvd);

       

       

       

       

      //DROP Table Historicals;

       

       

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

       

      Thanks a lot!

       

       

      -Ron

        • Re: Loop Help Please
          Fernando Tonial

          You can try this.

           

          Just one load for all files.

          Change

          [..\..\Data\FlatFiles\Historicals\2015 ADP Week $(i).xls]   //$(i)

          For

          [..\..\Data\FlatFiles\Historicals\2015 ADP Week*.xls]

           

          Historicals:

          LOAD

               Mid(SubField([Labor Account], '/', 3), 4, 6) AS [Cost Center],

               [Pay Code],

               Money,

               Hours,

               Wages,

               Days,

               $(i) as FiscalWeek

               //date($(vRipDate)) as CalendarDate

          FROM

          [..\..\Data\FlatFiles\Historicals\2015 ADP Week*.xls]

          (biff, embedded labels, header is 9 lines, table is Sheet1$)

          Where not IsNull([Labor Account])  //<> '' to get rid of blank cost centers?  //not isnull() is probably doing nothing

          ;

           

          Don't Worry, be Qlik.

          Tonial.

            • Re: Loop Help Please
              Ron Payne

              Hi Fernando,

               

               

              Thank you for the suggestion but i need the $(i) variable to populate the FiscalWeek so that i can link the data with our fiscal calendar. I was using a VB script that scans the directory ahead of time to read all of the file names. I was trying to think of a way to strip the week number off the end of the file and fill it in the data load that way. Any thoughts there?

                • Re: Loop Help Please
                  Fernando Tonial

                  Hi Ron,

                   

                  You can use this functions,  SubField() and FileBaseName().

                   

                  Like this:

                   

                  Historicals:

                  LOAD

                      Mid(SubField([Labor Account], '/', 3), 4, 6) AS [Cost Center],

                      [Pay Code],

                      Money,

                      Hours,

                      Wages,

                      Days,

                      SubField(FileBaseName(),' ',-1) as FiscalWeek

                      //date($(vRipDate)) as CalendarDate

                  FROM

                  [..\..\Data\FlatFiles\Historicals\2015 ADP Week*.xls]

                  (biff, embedded labels, header is 9 lines, table is Sheet1$)

                  Where not IsNull([Labor Account])  //<> '' to get rid of blank cost centers?  //not isnull() is probably doing nothing

                   

                  Don't worry, be Qlik.

                  Tonial.