Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to use if statement before a loop takes place

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

8 Replies
mohamed_ahid
Partner - Specialist
Partner - Specialist

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
Champion
Champion

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
Luminary Alumni
Luminary Alumni

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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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
Partner - Specialist
Partner - Specialist

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

Good luck

datanibbler
Champion
Champion

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
Partner - Champion III
Partner - Champion III

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