5 Replies Latest reply: May 11, 2016 6:17 AM by PAUL YEO RSS

    How to make use of FOR and NEXT to reduce 10 load script into 1 ?

    PAUL YEO

      Hi All

       

      I have below 2 sample load script . in fact my actual script i have 10 similar data structure. may i know how to make use of

      For and Next command to reduce the overall 10 load into 1 ?

       

       

       

      GL_TABLE:      // first line of the code is to give the table a name

       

       

      LOAD// Load meaning to read the raw data file.
      'TDSS' as SOURCE,
      date#(@58:77T,'DD/MM/YYYY')  as date,
      AutoNumberHash128(@58:77T,'TDSS') As Link_GL_Key,
      if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]                          // This will join the sign and AMT and create new var = AMOUNT.

       

       

      FROM $(vRAWPath)$(vFile63) (ansi, fix, no labels, header is 0, record is line);

       

       

      Concatenate

       

       

      LOAD// Load meaning to read the raw data file.
      'TDSKL' as SOURCE,
      date#(@58:77T,'DD/MM/YYYY')  as date,
      AutoNumberHash128(@58:77T,'TDSKL') As Link_GL_Key,
      if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]                          // This will join the sign and AMT and create new var = AMOUNT.

       

       

      FROM $(vRAWPath)$(vFile64) (ansi, fix, no labels, header is 0, record is line);

        • Re: How to make use of FOR and NEXT to reduce 10 load script into 1 ?
          Sunny Talwar

          Something along this line:

           

          GL_TABLE:

          LOAD 0 as Dummmy

          AutoGenerate 0;

           

          FOR i = 63 to 72

           

            Concatenate (GL_TABLE)

            LOAD 'TDSS' as SOURCE,

            Date#(@58:77T,'DD/MM/YYYY')  as date,

            AutoNumberHash128(@58:77T,$(i)) As Link_GL_Key,

            If(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]

            FROM $(vRAWPath)$(vFile$(i))

            (ansi, fix, no labels, header is 0, record is line);

           

          NEXT i;

           

          The thing to note is that you will have to somehow dynamically get the SOURCE because I don't have access to your actual data.

            • Re: How to make use of FOR and NEXT to reduce 10 load script into 1 ?
              PAUL YEO

              Hi sunny

               

              This is a good hint for me to kick start. As I can change TDSS to number and also file name to number.

               

              I will try it.

               

              Paul

               

              Sent from my iPhone

              • Re: How to make use of FOR and NEXT to reduce 10 load script into 1 ?
                PAUL YEO

                Hi Sunny

                 

                 

                I have below script which is runing well :-

                 

                 

                SET vDevelopment = 0;

                IF $(vDevelopment) = 0 THEN

                SET vRAWPath =  'D:\AUTO_LOAD\'; //local folder

                SET vFile50 = '60';

                set vFile51 = '61';

                else

                END IF

                 

                 

                GL_TABLE:    

                 

                 

                LOAD

                'TDSS' as SOURCE_GL,

                '60' as SOURCE,

                date#(@58:77T,'DD/MM/YYYY')  as date,

                AutoNumberHash128(@58:77T,'60') As Link_GL_Key,

                @124:129T  as [Reporting Code],

                if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]  

                FROM $(vRAWPath)$(vFile50).txt (ansi, fix, no labels, header is 0, record is line);

                 

                 

                Concatenate

                 

                 

                LOAD

                'TDSID' as SOURCE_GL,

                '61' as SOURCE,

                date#(@58:77T,'DD/MM/YYYY')  as date,

                AutoNumberHash128(@58:77T,'61') As Link_GL_Key,

                @124:129T  as [Reporting Code],

                if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]                    

                FROM $(vRAWPath)$(vFile52).txt (ansi, fix, no labels, header is 0, record is line);

                 

                 

                 

                 

                When press the intial select , i expect to get -111135.02

                 

                 

                 

                 

                // BELOW IS I TRY BUT GET WRONG VALUE

                 

                 

                GL_TABLE:

                LOAD 0 as Dummmy

                AutoGenerate 0;

                 

                FOR i = 60 to 61

                 

                  Concatenate (GL_TABLE)

                  LOAD

                  '$(i)' as SOURCE,

                  'TDSS' as SOURCE_GL,

                  Date#(@58:77T,'DD/MM/YYYY')  as date,

                  AutoNumberHash128(@58:77T,$(i)) As Link_GL_Key,

                  @124:129T  as [Reporting Code],

                  If(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount]

                  FROM $(vRAWPath)$(vFile50).txt (ansi, fix, no labels, header is 0, record is line);

                 

                 

                NEXT i;

                 

                 

                I get the wrong result . May i know why ?

                 

                 

                I have enclosed a simple file in the zip file , pls unzip it and copy to Drive D , folder name AUTO.

                 

                 

                Paul

              • Re: How to make use of FOR and NEXT to reduce 10 load script into 1 ?
                Damian Waldron

                Do you need to loop? You can use a * to indicate to Qlik to load all files.

                 

                I.e.

                FROM $(vRAWPath)*(ansi, fix, no labels, header is 0, record is line);