Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Script: Using Variables, FOR loops & IF statements

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?

Tags (3)
9 Replies
Highlighted
Partner
Partner

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

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

Highlighted

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

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)



Highlighted
Partner
Partner

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

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.

Highlighted
Partner
Partner

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

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

Highlighted

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

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

Highlighted
Partner
Partner

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

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

Highlighted

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

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

Highlighted
Partner
Partner

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

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

Highlighted

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

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