topic For...next loop in QlikView

For...next loop

Wed, 17 Jun 2015 15:22:47 GMT

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

Wed, 17 Jun 2015 15:35:43 GMT

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 — Wed, 17 Jun 2015 15:42:37 GMT

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 — Wed, 17 Jun 2015 15:46:42 GMT

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

petter — Wed, 17 Jun 2015 15:52:13 GMT

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

maxgro — Wed, 17 Jun 2015 18:08:13 GMT

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

Fri, 19 Jun 2015 15:51:29 GMT

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

Peter_Cammaert — Fri, 19 Jun 2015 16:07:11 GMT

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