0 Replies Latest reply: Mar 6, 2013 1:07 PM by wilfried.kopp RSS

    Generating a table using a loop



      I am facing the following problem: I need to generate a table looking like this:

      Date0:00... 10:0011:0012:0013:00...23:00


      Row are dates.

      Columns are hours of the day (0...23)

      The numbers inside the tables are the amount of people available ( that´s the challenge!)


      I need to build this and I have:

      - a table with users including names and country

      - a table of bank holidays per country

      - a table with working time per country

      - a table of holidays per user


      To get started, I generated a calendar to cover the range I need.

      Loading the tables above is no problem of course.


      Can you explain what would be the best approach to fill this table ?


      My idea (but I miss some elements to acheive it) is to:

      - generate a calendar => done

      - load all the data => done

      - use for loop to loop thru all the days => done

      - use a nested for loop for the hours (from 0 to 23) => should be ok

      - another for loop to go thru the users and check if the user is availble, that day, at that time (can we define functions in Qlikview ?)

      - finally sum the results per user (or keep separate tables)

      - put all of that together !


      The function above will need to check out value from all the table to define if a user is available or not.


      The first problem I hit is how to create a table with data from a for loop (I proudly acheived to generate 365 tables dynamically but that does not help me )


      How would you approach this problem? Does Qlikview scripting makes sense in that case?



      My Calendar is generated as (this part is probably OK):

      LET Start = floor(YearStart(AddMonths(today(), -12*0))); // *N = N year in the past

      LET End = floor(YearEnd(AddMonths(today(), 12*0)));  // *N = N year in the future


      LET NumOfDays = End - Start + 1;




      $(Start) + Rowno() -1 as DateId

      AUTOGENERATE $(NumOfDays);




      date(DateId) as Date, // it will be in format defined in your SET DateFormat=, or in your system format

      day(DateId) as Day,

      month(DateId) as Month, // simple month name; it is dual - numeric and text

      dual(month(DateId) & '-' & year(DateId), year(DateId) & num(month(DateId), '00')) as MonthYear, // Month-Year format, dual

      dual(month(DateId) & '-' & day(DateId), year(DateId) & num(month(DateId), '00') & num(day(DateId), '00')) as MonthDay, // Month-Year format, dual

      year(DateId) as Year,


      RESIDENT Date_src;


      Drop Table Date_src;


      LET Start = null();

      LET End = null();

      LET NumOfDays = null();


      For now, I am messing around with (dont use that at home! ):


      LOAD DateId as %id

      resident Calendar;


      for i = 1 to NoOfRows('Calendar')-1

                id = peek('DateId',$(i),'Calendar');

                h = 9; // just a test


                Load $(id) as %id, $(h)&$(id) as test


      //          ConcatTable:

      //          LOAD $(d)&$(h) as TT AutoGenerate(1);

      //          Resident Calendar

      //  Where DateId = '$(d)';