How to use if statement before a loop takes place

[Anonymous]

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

[mohamed_ahid]

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

.......

[datanibbler]

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

[jagan · Accepted solution]

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.

[Anonymous]

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.

[Peter_Cammaert]

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

[mohamed_ahid]

be careful if there is a space in the file's name the mid function will retunrs fals results .

Good luck

[datanibbler]

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

[Peter_Cammaert]

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