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

    Generating a table using a loop

      Hello,

       

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

      Date0:00... 10:0011:0012:0013:00...23:00
      01/01/1300444400
      02/01/13...
      3334.....
      ......
      .................
      31/12/130
      22200...









       

      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;

       

      Date_src:

      LOAD

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

      AUTOGENERATE $(NumOfDays);

       

      Calendar:

      LOAD

      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! ):

      Availability:

      LOAD DateId as %id

      resident Calendar;

       

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

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

                h = 9; // just a test

                JOIN

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

                AutoGenerate(1);

      //          ConcatTable:

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

      //          Resident Calendar

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

      Next