topic Re: How to use if statement before a loop takes place in QlikView

How to use if statement before a loop takes place

Anonymous — Tue, 21 Apr 2015 09:02:44 GMT

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

Re: How to use if statement before a loop takes place

mohamed_ahid — Tue, 21 Apr 2015 09:11:22 GMT

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

.......

Re: How to use if statement before a loop takes place

datanibbler — Tue, 21 Apr 2015 09:12:34 GMT

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

Re: How to use if statement before a loop takes place

jagan — Tue, 21 Apr 2015 09:12:36 GMT

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.

Re: How to use if statement before a loop takes place

Anonymous — Tue, 21 Apr 2015 09:27:02 GMT

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.

Re: How to use if statement before a loop takes place

Peter_Cammaert — Tue, 21 Apr 2015 09:34:52 GMT

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);

While it works perfectly, unfortunately the syntax checker does not like it. But it makes compact code possible...

Good luck,

Peter

Re: How to use if statement before a loop takes place

mohamed_ahid — Tue, 21 Apr 2015 09:38:30 GMT

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

Good luck

Re: How to use if statement before a loop takes place

datanibbler — Tue, 21 Apr 2015 09:38:36 GMT

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

Re: How to use if statement before a loop takes place

Peter_Cammaert — Tue, 21 Apr 2015 10:10:48 GMT

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