topic Re: Script: Using Variables, FOR loops & IF statements in QlikView

Script: Using Variables, FOR loops & IF statements

ciaran_mcgowan — Tue, 20 Sep 2016 14:44:29 GMT

Hi all,

I have a scenario where I need Qlikview to do the following:

Read all filenames in a folder
Store the latest file name as a variable (file name contains dates)
Store the number of files in the folder as a variable
Store how old the last created fie as a variable (in days)
Using these 3 variables, use an IF statement to determine what the values in a FOR loop are.

I have parts 1-4 working but I just can't figure out 5. My scripts are as follows:

Latest_File:
LOAD 
  Mid(FileBaseName(), 10, 😎 AS FileName
FROM 
[..\QVD\1 Extract\E_Report *.qvd];

LET vLatestFile = 'Max(FileName)';

LET vCountFile = 'Count(DISTINCT FileNmae)';

LET vGroupFile = 'Year((Today()) - $(vLatestFile)';

Below is what I has for 5 but it's not working;

IF $(vCountFile) = 0
FOR i = Date#(01/01/2000, 'DD/MM/YYYY') TO Date#(31/12/2000, 'DD/MM/YYYY')
ELSEIF $(vGroupFile) > 5 THEN
FOR i = $(vLatestSeason) TO $(vLatestSeason) + 5
ELSE $(vGroupGroup) <= 5 THEN
FOR i = $(vLatestSeason) TO Today()
ENDIF

The first part should run if the folder is empty, but only loading the first year of data. The second if will the files load files if the latest one is more than 5 years old - files are very big so this limits the application to pull in no more than 5 years at once. The third part loads all files created within the last five years.

Can anyone help?

Re: Script: Using Variables, FOR loops & IF statements

Anonymous — Tue, 20 Sep 2016 14:49:08 GMT

you Need to work with peek

e.g.

LET vLatestFile = peek('FileName',0)

where 0 idicates first row

you may sort the tabvle before peek and then use peek to get first or last row

instead of Count you may use NoOfRows('Latest_File')

Re: Script: Using Variables, FOR loops & IF statements

rubenmarin — Tue, 20 Sep 2016 15:18:39 GMT

To explain the peek part...

Latest_File:

LOAD

Max(Mid(FileBaseName(), 10, 8)) AS MaxFileName ,

Count(Distinct FileBaseName()) as CountFileName

FROM

[..\QVD\1 Extract\E_Report *.qvd];

Then you can assign it to a variable:

LET vLatestFile = Num(Date#(peek('MaxFileName',0), 'YYYYMMDD')); //changed to num so you can calculate the difference in vGroupFile

LET vCountFile = peek('CountFileName',0)

Re: Script: Using Variables, FOR loops & IF statements

ciaran_mcgowan — Tue, 20 Sep 2016 15:20:16 GMT

Hi Rudolf,

Thank you but this isn't the part I'm having an issue with. I can already get the latest file loaded. It is the IF/FOR loop in #5 that I can't get to work.

Re: Script: Using Variables, FOR loops & IF statements

ciaran_mcgowan — Tue, 20 Sep 2016 15:24:24 GMT

Sorry, but the approach I have for #1-4 all work. It's #5 I'm having the issue with.

Re: Script: Using Variables, FOR loops & IF statements

rubenmarin — Tue, 20 Sep 2016 15:30:23 GMT

Hi Ciarán, please check in the reload log the values passed to the variables, maybe it doesn't returns an error but I bet it's not using the expected values in step 5.

Specially Today()-$(vLatestFile) won't give you the dates difference, if vLatestFile is 20160919, it will substract 20.160.919 days to today().

Re: Script: Using Variables, FOR loops & IF statements

ciaran_mcgowan — Tue, 20 Sep 2016 15:38:40 GMT

Hi,

Sorry but there is a typo in part 1.

Instead of Mid(FileBaseName(), 10, 😎 AS FileName it should read Mid(FileBaseName(), 10, 4) AS FileName. I am subtracting years, not dates. The variables are returning exactly what I'd expect in the front end so that's why I'm convinced I've made a stupid syntax error in #5.

Thanks

Re: Script: Using Variables, FOR loops & IF statements

Peter_Cammaert — Tue, 20 Sep 2016 15:40:37 GMT

Just a question. Is the code you posted for part 5 really present in your script? Because that isn't legal QlikView Script code.

A correct way to write different FOR loop ranges is something like this (pseudo code)

IF condition_1 THEN

LET vLoopStart = expression1;

LET vLoopEnd = expression2;

ELSEIF condition_2 THEN

LET vLoopStart = expression3;

LET vLoopEnd = expression4;

ELSE // Default FOR loop values

LET vLoopStart = expression5;

LET vLoopEnd = expression6;

END IF

FOR i = vLoopStart TO vLoopEnd

Statements

Best,

Peter

Re: Script: Using Variables, FOR loops & IF statements

ciaran_mcgowan — Tue, 20 Sep 2016 15:52:38 GMT

It was yes but it there were some typos (e.g. missing THEN in the first part).

Re: Script: Using Variables, FOR loops & IF statements

Peter_Cammaert — Tue, 20 Sep 2016 16:05:11 GMT

Try with this code:

IF vCountFile = 0 THEN

LET vLoopStart = MakeDate(2000, 1, 1);

LET vLoopEnd = MakeDate(2000, 12, 31);

ELSEIF vGroupFile > 5 THEN

LET vLoopStart = vLatestSeason;

LET vLoopEnd = vLatestSeason + 5;

ELSEIF vGroupGroup <= 5 THEN

LET vLoopStart = vLatestSeason;

LET vLoopEnd = num(Today());

END IF

FOR i = vLoopStart TO vLoopEnd

A few remarks concerning this code:

The loop boundaries aren't set if none of the three IF conditions are met. There is no final ELSE clause. Are you sure about this?
Condition 2 uses a variable called vGroupFile. Condition 3 uses a variable called vGroupGroup that matches all remaining values. Are you sure about this?

Best,

Peter