9 Replies Latest reply: Sep 20, 2016 12:05 PM by Peter Cammaert RSS

    Script: Using Variables, FOR loops & IF statements

    Ciarán 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:



        Mid(FileBaseName(), 10, 8) AS FileName
      [..\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()


      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?