Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
Your problem is this line:
For date=$(minimum) to $(maximum) step 1
replace it with this:
For date=minimum to maximum step 1
$(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.
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;
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...
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