topic Re: Re: iterno() question in QlikView

iterno() question

amien — Wed, 29 Oct 2014 15:19:12 GMT

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

Anonymous — Wed, 29 Oct 2014 20:05:46 GMT

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 — Wed, 29 Oct 2014 20:13:56 GMT

Those , were lost during my copy/paste action. The script itself runs fine, i just have 0 results

Re: iterno() question

amien — Fri, 31 Oct 2014 06:23:54 GMT

anyone?

Re: iterno() question

jonathandienst — Fri, 31 Oct 2014 06:31:14 GMT

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 — Fri, 31 Oct 2014 22:28:24 GMT

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: iterno() question

Anonymous — Fri, 31 Oct 2014 23:50:22 GMT

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;

Re: Re: iterno() question

MarcoWedel — Sat, 01 Nov 2014 00:18:19 GMT

Hi,

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

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 — Sat, 01 Nov 2014 11:13:51 GMT

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

Anonymous — Sat, 01 Nov 2014 12:53:10 GMT

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