9 Replies Latest reply: Nov 1, 2014 8:56 AM by Nicolai Jørgensen RSS

    iterno() question

    Amien Amien

      i don't quite get it.. having this LOAD script:

       

       

      LET vStartDate = date(date#(today(),'YYYY-MM-DD'));
      LET vEndDate = addmonths(date(date#(today(),'YYYY-MM-DD')),1);

       

      LOAD

           Value,

           Counter,

           date([start date + iterno()-1,'YYYY-MM-DD') AS [%Date]

      RESIDENT Facts

      WHILE
      date([start date] + iterno()-1,'YYYY-MM-DD') >= $(vStartDate)
      AND
      date([end date] + iterno()-1,'YYYY-MM-DD') < $(vEndDate) 
      ;

       

      Why doesn't this work? What i want is a record for each day an employee is under contract (start/end date).

      But i only want records until the upcoming month (otherwise it will create too many records)

       

      Thanks in advanced

        • Re: iterno() question
          Bill Markham

          Do you need some commas, as in :

           

          LET vStartDate = date(date#(today(),'YYYY-MM-DD'));
          LET vEndDate = addmonths(date(date#(today(),'YYYY-MM-DD')),1);

           

          LOAD

              Value ,

              Counter ,

              date([start date + iterno()-1,'YYYY-MM-DD') AS [%Date]

          RESIDENT Facts

          WHILE
          date([start date] + iterno()-1,'YYYY-MM-DD') >= $(vStartDate)
          AND
          date([end date] + iterno()-1,'YYYY-MM-DD') < $(vEndDate) 
          ;

          • Re: iterno() question
            Amien Amien

            anyone?

            • Re: iterno() question
              Jonathan Dienst

              Hi

               

              Either change the variables to number format - Option 1:

               

              LET vStartDate = Num(today());

              LET vEndDate = Num(addmonths(today(), 1));

               

              Or add quotes around the dates when you use them - Option2:

               

              WHILE

              date([start date] + iterno()-1,'YYYY-MM-DD') >= '$(vStartDate)'

              AND

              date([end date] + iterno()-1,'YYYY-MM-DD') < '$(vEndDate)'

               

              Use just one of the above two options!

               

              HTH

              Jonathan

                • Re: iterno() question
                  Amien Amien

                  Thanks,

                   

                  I have results, but what does this really show?

                   

                  What i need is that if an employee started at 01-01-2014 and has an end date of 01-01-2015, given the above vStartDate and vEndDate .. the result should be 1 hit on each day for this employee. 30 records in total

                    • Re: Re: iterno() question
                      Marco Wedel

                      Hi,

                       

                      still I'm not quite sure about what you like to achieve, but maybe this helps:

                       

                      QlikCommunity_Message_140015_Pic1.JPG.jpg

                       

                      SET DateFormat='YYYY-MM-DD';
                      
                      Facts:
                      LOAD * INLINE [
                          Value, Counter, start date, end date
                          1234, 1, 2014-01-01, 2015-01-01
                          2345, 2, 2014-02-10, 2015-05-01
                          3456, 3, 2014-03-20, 2014-10-01
                          4567, 4, 2014-06-30, 2014-09-01
                          5678, 5, 2014-07-31, 2016-12-01
                          6789, 6, 2014-08-31, 2015-12-01
                          7890, 7, 2014-09-31, 2015-09-01
                          8901, 8, 2014-11-01, 2015-08-01
                          9012, 9, 2014-11-15, 2015-07-01
                          0123, 10, 2014-11-30, 2015-06-01
                      ];
                      
                      SET vStartDate = Today();
                      SET vEndDate = AddMonths(Today(),1);
                      
                      Dates:
                      LOAD Value,
                           Counter,
                           Date([start date]+IterNo()-1) as [%Date]
                      Resident Facts
                      While [start date]+IterNo()-1>=$(vStartDate) and [start date]+IterNo()-1<$(vEndDate);
                      

                       

                       

                      I changed the LET vStartDate to SET vStartDate (same for vEndDate).

                      Your condition only seemed true for Start Dates > Today and End Dates < Today + 1 Month, so your example 01-01-2014-01-01-2015 would not create any Dates.

                       

                      Maybe you can explain using a sample table?

                       

                      hope this helps

                       

                      regards

                       

                      Marco

                        • Re: Re: Re: iterno() question
                          Amien Amien

                          Hi Marco,

                           

                          Your code will only keep the 8901,9012 and 0123 records. And i'm expecting more records.

                           

                          What i need to see:


                          step 1 : per day how many active employees there are. On 2014-11-01 i'm not expecting 1 employee but 6 employees (2 employees have an end date before 2014-11-01


                          step 2 : because it's a LOT of data, i just need a snapshot of the coming month. otherwise too many records will be created and the load script will take too long.


                          what i could do is an intervalmatch to create ALL the records, and then this is done, load the data again with a WHERE %Date >= $(vStartDate)  and %Date <= $(vEndDate).


                          But i'm looking for a more efficient way, without loading the complete interval match table


                          Facts: 
                          LOAD * INLINE
                               EmployeeID, Counter, start date, end date 
                               1234, 1, 2014-01-01, 2015-01-01 
                               2345, 1, 2014-02-10, 2015-05-01 
                               3456, 1, 2014-03-20, 2014-10-01 
                               4567, 1, 2014-06-30, 2014-09-01 
                               5678, 1, 2014-07-31, 2016-12-01 
                               6789, 1, 2014-08-31, 2015-12-01 
                               7890, 1, 2014-09-31, 2015-09-01 
                               8901, 1, 2014-11-01, 2015-08-01 
                               9012, 1, 2014-11-15, 2015-07-01 
                               0123, 1, 2014-11-30, 2015-06-01 
                          ]
                            
                          SET vStartDate = Today(); 
                          SET vEndDate = AddMonths(Today(),1); 
                            
                          Dates: 
                          LOAD Value
                               
                          Counter
                               
                          Date([start date]+IterNo()-1) as [%Date] 
                          Resident Facts 
                          While [start date]+IterNo()-1>=$(vStartDate) and [start date]+IterNo()-1<$(vEndDate)

                          DROP TABLE Facts;

                            • Re: Re: Re: iterno() question
                              Nicolai Jørgensen

                              Take a look at my script if not already. Your while will stop too soon because of this is not true [start date]+IterNo()-1>=$(vStartDate).

                              1) I suggest generate records while [end date] is less than vEndDate as a preceeding load and then do a where to assure you only keep the records where [start date] is greater than or larger than start vStartDate.

                              Now testing that the dates are both in the interval of the employee's start and end and in the vStart and vEnd.

                               

                              Facts:

                              Load *

                              where [%Date] >= '$(vStartDate)'

                                AND [%Date] < '$(vEndDate)'

                                AND [%Date] < [end date]

                                AND [%Date] >= [start date];

                              LOAD EmployeeID, Counter,

                                   [end date], [start date],

                                   date([start date] + IterNo()-1 ) AS [%Date]

                              RESIDENT Facts

                              WHILE date([start date] + IterNo()-1) < '$(vEndDate)';


                              2) You can also just generate a date table with all dates from min to max in facts and join to facts table with no keys, then do a where to keep the date ranges. If you dont have a million employees I doubt it's taking long.



                              /Nicolai


                      • Re: iterno() question
                        Nicolai Jørgensen

                        Hi

                        First of all, it's unclear to me if you have [start date] and [end date] in the Facts table? I assume you do. Looks like you're missing some fields and at least a ] after start date in line 7: "date([start date + iterno()-...."

                        You will also be getting synthetic keys if not dropping some fields or a table. Assuming you do that somewhere in your script, I could make it run fine with this (I do not have your Facts table so I just created one). It creates 30 rows.

                        I want to generate records where the [start date] of the employee + iterno() is less than the set end date variable vEndDate. Since it's a while clause it only does it while the statement is true, meaning, in your script your [start date] value in the table is most likely older than the set start date vStartDate (since most employees are not hired current month), it will be false from beginning = 0 records. I therefor use the approach where I generate all date records from employee [start date] util set vEndDate, then do a where clause to keep desired date range. You might be able to write it another way to limit generated dates, but I tried to keep it as close to your idea as possible:


                        LET vStartDate = today();

                        LET vEndDate = addmonths(today(),1);

                         

                        // Just some example data, assuming you have employees table with start and end date. Since end date might be blank (still employed) I took that into account assuming the employee is at least here for end of month plus 1

                        Facts:

                        Load Value,

                          Counter,

                          date(date#([start date],'DDMMYYYY' )) as [start date],

                          date(alt(date#([end date], 'DDMMYYYY'), monthend(Today(), 1))) as [end date]

                        inline [Value, Counter, start date, end date

                          xyz, 1, "01062011", ];

                         

                         

                        // I just gave it a random name. Do you want to rename the fields or do you drop on of the tables or fields?

                        Facts2:

                        Load *

                        where [%Date] >= '$(vStartDate)'

                          AND [%Date] < '$(vEndDate)';

                        AND TEST ALSO FOR %Date is less than [end date]

                        LOAD

                             Value,

                             Counter, [end date],

                             date([start date] + IterNo()-1 ) AS [%Date]

                        RESIDENT Facts

                        WHILE date([start date] + IterNo()-1) < '$(vEndDate)';

                         

                        Drop table Facts;