Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone.
I have a for each loop within a QV app that reads monthly files.
each csv has the month extension next to it, for example file_201101.csv, file_201102.csv, file_201103.csv, etc.
my loop looks as follows:
for each File in filelist $(path)file_*.csv
"TABLE":
LOAD date
FROM $(File) (txt, ansi, embedded labels, delimiter is ',', no quotes);
next
What I wish to do is to only load files that has a month extension bigger than 201101, without having to delete the physical csv's on our server.
thank you so much,
Stefan
Hi,
Try like this
for each File in filelist $(path)file_*.csv
IF Num(Mid('file_201101.csv', 6, 6)) > 201101 THEN
"TABLE":
LOAD date
FROM $(File) (txt, ansi, embedded labels, delimiter is ',', no quotes);
ENDIF
next
Hope this helps you.
Regards,
Jagan.
Hi
use this before your loop to keep only numbers and then youc can make test over it .
if KeepChar('file_201102.csv','0123456789')> 201101
then
.......
Hi Stefan,
just put that loop inside an IF_THEN clause where you query that number (201101) or just the middle two (11 in this case) to execute the loop only on those - or you might have to put it inside the loop, but around the LOAD, I'm not sure.
Probably the latter - that way the loop will still parse all files, but load only those that you want.
HTH
Hi,
Try like this
for each File in filelist $(path)file_*.csv
IF Num(Mid('file_201101.csv', 6, 6)) > 201101 THEN
"TABLE":
LOAD date
FROM $(File) (txt, ansi, embedded labels, delimiter is ',', no quotes);
ENDIF
next
Hope this helps you.
Regards,
Jagan.
Hi, thank you for your reply.
I did almost in the same way. the only thing was that I put a semicolon after endif.
when I took that out, my app loaded perfectly.
Note that there is a nice keyword in QV Script called UNLESS. If you use that one, your script becomes:
FOR EACH File IN filelist ('$(path)file_*.csv')
UNLESS Num#(Mid(Subfield('$(File)', '\', -1), 6, 6)) < 201101
TABLE: LOAD date FROM [$(File)] (txt, ansi, embedded labels, delimiter is ',', no quotes);
NEXT
While it works perfectly, unfortunately the syntax checker does not like it. But it makes compact code possible...
Good luck,
Peter
be careful if there is a space in the file's name the mid function will retunrs fals results .
Good luck
Hi Peter,
that is an issue that I hope Qlik will eventually resolve.
There are also some operators in set_analysis (like the -=) that the syntax_checker does not recognize, so that - and all the code following - is underlined in red. That is quite annoying if you have a lengthy formula because there's no way to tell whether the remainder after that one piece is correct or not ... that's why I don't use those things although they would make the code more compact and more elegant.
Best regards,
DataNibbler
Hi DataNibbler,
my scripts are often filled with these wiggly lines, unforntunately. Ever tried adding LOAD prefixes by way of a variable to change an explicit table name (on the very first run) into a CONCATENATE (Tablename) (on all subsequent runs)? You'll promptly get an angry LOAD statement looking back at you. But it does work.
If you skip all syntax checker-averse but legal code in QlikView you're missing out on a lot of interesting (and efficient) techniques. That's one of the reasons why I use an external editor as much as possible. Haven't found a solution for expressions though...
Best,
Peter