Script: Using Variables, FOR loops & IF statements

[ciaran_mcgowan]

Hi all,

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

1. Read all filenames in a folder
2. Store the latest file name as a variable (file name contains dates)
3. Store the number of files in the folder as a variable
4. Store how old the last created fie as a variable (in days)
5. 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:

1.

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

2.

LET vLatestFile = 'Max(FileName)';

3.

LET vCountFile = 'Count(DISTINCT FileNmae)';

4.

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?

[Anonymous]

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

[rubenmarin]

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)

[ciaran_mcgowan]

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.

[ciaran_mcgowan]

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

[rubenmarin]

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().

[ciaran_mcgowan]

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

[Peter_Cammaert]

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

NEXT

Best,

Peter

[ciaran_mcgowan]

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

[Peter_Cammaert]

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

  :

NEXT

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