Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

For...next loop

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

Tags (2)
7 Replies
Not applicable

Re: For...next loop

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

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;

MVP
MVP

Re: For...next loop

Your problem is this line:

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


replace it with this:


For date=minimum to maximum step 1



MVP
MVP

Re: For...next loop

$(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.

MVP
MVP

Re: For...next loop

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;

Not applicable

Re: For...next loop

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

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

Community Browser