7 Replies Latest reply: Jun 19, 2015 12:07 PM by Peter Cammaert RSS

    For...next loop

    Martijn Noorda

      Hello,

       

      I wrote the following loop to load all files in a specific folder per date. However, the script does not load my files but it does not result in any errors either. Could anyone please explain what is wrong about the following script and how I should correct it? Thanks!

       

      let vPath=C:\........\

      let vDTCCFX=PositionReportFX

      let vDTCCRATES=PositionReportRATES

      let vDTCCCREDIT=PositionReportCREDIT

      kkj:

      load

      date(max(date),'YYYYMMDD') as maximum,

      date(min(date),'YYYYMMDD') as minimum

      resident table1 order by date;


      let maximum = date(peek('maximum',0,'kkj'),'DDMMYYYY');

      let minimum = date(peek('minimum',0,'kkj'),'DDMMYYYY');

       

      For date=$(minimum) to $(maximum) step 1


      PositietabelDTCC:

      LOAD

      [UTI Prefix],

      [UTI Prefix]&[UTI Value] as [UTI Value],

      [Trade Party 1 Transaction Id] as [Transaction No],

      replace("Notional amount leg 1 [14]",'.',',') as DTCCNominal1,

      replace("Notional amount leg 2 [14a]",'.',',') as DTCCNominal2,

      date("Maturity date [21]",'DD/MM/YYYY') as [Maturity Date],

      date("Effective date leg 1 [20]",'DD/MM/YYYY') as [Trade Date],

      "Reporting entity ID [9]",

      [ESMA Match Status],

      [Trade Party 1 Value]


      FROM

      $(vPath)$(vDTCCFX)$(date).csv

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

       

      LOAD

      [UTI Prefix],

      [UTI Prefix]&[UTI Value] as [UTI Value],

      [Trade Party 1 Transaction Id] as [Transaction No],

      replace("Notional amount leg 1 [14]",'.',',') as DTCCNominal1,

      replace("Notional amount leg 2 [14a]",'.',',') as DTCCNominal2,

      date("Maturity date [21]",'DD/MM/YYYY') as [Maturity Date],

      date("Effective date leg 1 [20]",'DD/MM/YYYY') as [Trade Date],

      [ESMA Match Status],

      "Reporting entity ID [9]",

      [Trade Party 1 Value]


      FROM $(vPath)$(vDTCCRATES)$(date).csv

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

       

      LOAD

      [UTI Prefix],

      [UTI Prefix]&[UTI Value] as [UTI Value],

      [Trade Party 1 Transaction Id] as [Transaction No],

      replace("Notional amount leg 1 [14]",'.',',') as DTCCNominal1,

      replace("Notional amount leg 2 [14a]",'.',',') as DTCCNominal2,

      date("Maturity date [21]",'DD/MM/YYYY') as [Maturity Date],

      date("Effective date leg 1 [20]",'DD/MM/YYYY') as [Trade Date],

      [ESMA Match Status],

      "Reporting entity ID [9]",

      [Trade Party 1 Value]


      FROM $(vPath)$(vDTCCCREDIT)$(date).csv

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

       

      FinalDTCC:


      load *,

      text(text(DTCCNominal1*DTCCNominal2)&[Trade Date]&[Maturity Date]) as [Identifier],

      $(date) as Timestamp

       

      resident PositietabelDTCC;

       

      NEXT date

        • Re: For...next loop
          Srikanth P

          HI, I didn't see any syntax issue. You need to bebug the code why its not working.

           

          1, First run the loop with any Load stamenst. Just write TRACE command and check your loop working aspected or not.

          2. Then comment the script & check Load statements are working fine for one specific File.

          3. Try to full run, Check the qvw log file for detail.

           

          If you still have please post more details on the application & whole script.

          • Re: For...next loop
            Sunny Talwar

            Not sure what could be wrong, but try this may be:

             

            let vPath=C:\........\

            let vDTCCFX=PositionReportFX

            let vDTCCRATES=PositionReportRATES

            let vDTCCCREDIT=PositionReportCREDIT

            kkj:

            load

            date(max(date),'YYYYMMDD') as maximum,

            date(min(date),'YYYYMMDD') as minimum

            resident table1 order by date;


            let maximum = date(peek('maximum',0,'kkj'),'DDMMYYYY');

            let minimum = date(peek('minimum',0,'kkj'),'DDMMYYYY');

             

            For date=$(minimum) to $(maximum) step 1


            PositietabelDTCC:

            LOAD *,

                      text(text(DTCCNominal1*DTCCNominal2)&[Trade Date]&[Maturity Date]) as [Identifier],

                      $(date) as Timestamp;

            LOAD

            [UTI Prefix],

            [UTI Prefix]&[UTI Value] as [UTI Value],

            [Trade Party 1 Transaction Id] as [Transaction No],

            replace("Notional amount leg 1 [14]",'.',',') as DTCCNominal1,

            replace("Notional amount leg 2 [14a]",'.',',') as DTCCNominal2,

            date("Maturity date [21]",'DD/MM/YYYY') as [Maturity Date],

            date("Effective date leg 1 [20]",'DD/MM/YYYY') as [Trade Date],

            "Reporting entity ID [9]",

            [ESMA Match Status],

            [Trade Party 1 Value]


            FROM

            $(vPath)$(vDTCCFX)$(date).csv

            (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

             

            Concatenate(PositietabelDTCC)

            LOAD *,

                      text(text(DTCCNominal1*DTCCNominal2)&[Trade Date]&[Maturity Date]) as [Identifier],

                      $(date) as Timestamp;

            LOAD

            [UTI Prefix],

            [UTI Prefix]&[UTI Value] as [UTI Value],

            [Trade Party 1 Transaction Id] as [Transaction No],

            replace("Notional amount leg 1 [14]",'.',',') as DTCCNominal1,

            replace("Notional amount leg 2 [14a]",'.',',') as DTCCNominal2,

            date("Maturity date [21]",'DD/MM/YYYY') as [Maturity Date],

            date("Effective date leg 1 [20]",'DD/MM/YYYY') as [Trade Date],

            [ESMA Match Status],

            "Reporting entity ID [9]",

            [Trade Party 1 Value]


            FROM $(vPath)$(vDTCCRATES)$(date).csv

            (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

             

            Concatenate(PositietabelDTCC)

            LOAD *,

                      text(text(DTCCNominal1*DTCCNominal2)&[Trade Date]&[Maturity Date]) as [Identifier],

                      $(date) as Timestamp;

            LOAD

            [UTI Prefix],

            [UTI Prefix]&[UTI Value] as [UTI Value],

            [Trade Party 1 Transaction Id] as [Transaction No],

            replace("Notional amount leg 1 [14]",'.',',') as DTCCNominal1,

            replace("Notional amount leg 2 [14a]",'.',',') as DTCCNominal2,

            date("Maturity date [21]",'DD/MM/YYYY') as [Maturity Date],

            date("Effective date leg 1 [20]",'DD/MM/YYYY') as [Trade Date],

            [ESMA Match Status],

            "Reporting entity ID [9]",

            [Trade Party 1 Value]


            FROM $(vPath)$(vDTCCCREDIT)$(date).csv

            (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

             

            FinalDTCC:


            load *,

            text(text(DTCCNominal1*DTCCNominal2)&[Trade Date]&[Maturity Date]) as [Identifier],

            $(date) as Timestamp

             

            resident PositietabelDTCC;

             

            NEXT date;

            • Re: For...next loop
              Petter Skjolden

              Your problem is this line:

               

              For date=$(minimum) to $(maximum) step 1


              replace it with this:


              For date=minimum to maximum step 1



                • Re: For...next loop
                  Martijn Noorda

                  This doesn't seem to be the problem. If I change it, it still won't load any of my tables. Could it be that there is a problem due to the fact that there is not a file available for each date? I now used an if filesize(vDTCCFX$(date))>0 statement before the load statement, but the script still doesn't load any of my tables.

                   

                  I was thinking that maybe a 'for each' statement would serve my purpose better. But I am not sure how I should go about writing such a loop...

                • Re: For...next loop
                  Petter Skjolden

                  $(minimum) does a $-sign substitution so you will get probably something like this:

                   

                  For date='31052015' to '01062015' step 1    // Your Dual-type dates returns the Text part which look like this almost

                   

                  that will be interpreted further to

                   

                  For date=31052015 to 1062015 step 1         // QlikView translates the strings into numbers if possible

                   

                  Doing an integer iteration through date text values will not work in everything but a few special cases. Make sure that

                  you use the Number part of the dual-type dates and QlikView will happily iterate through each date correctly.

                  • Re: For...next loop
                    Massimo Grossi

                    loop with integer (date)

                    read file with date (date2)

                     

                     

                    table1:

                    load date(makedate(2015) + rowno()) as date

                    AutoGenerate 100;

                     

                    kkj:

                    load min(date) as minimum, max(date) as maximum resident table1;

                     

                    let maximum = peek('maximum',0,'kkj');

                    let minimum = peek('minimum',0,'kkj');

                    trace $(minimum), $(maximum);

                     

                    For date=$(minimum) to $(maximum)

                      let date2=date(date, 'DDMMYYYY');

                      // some trace to check date for loop and date2 for filename

                      trace $(date);

                      trace $(date2);

                     

                           // using date2

                           // add here your code for read from file

                          ........

                         FROM $(vPath)$(vDTCCCREDIT)$(date2).csv

                         ................

                    next;

                    • Re: For...next loop
                      Peter Cammaert

                      IMHO the best way to figure out what is working / what is not working is to use TRACE statements in your code. You can even make their execution flag-dependent, like for instance this one that should go immediately after the assignment of min/max dates to variables:

                       

                      IF '$(vDebug)' = '1' THEN

                        TRACE >>> Date boundaries : minimum = [$(minimum)], maximum = [$(maximum)] ;

                      END IF

                       

                      On a side note: using variables instead of $-sign substitution really has only one disadvantage: your log file won't show their contents, while $-sign substitution will show the substituted values in all statements.

                       

                      Best,

                       

                      Peter