topic Using peek and Date Calculations in QlikView

Using peek and Date Calculations

Thu, 04 Aug 2011 22:37:21 GMT

Hi Everyone,

I am trying to calculate how many days have passed since the previous event occured. I am using the peek function (works succesfully), but for some reason when I try and do calculations with my data it does not give me what I expect (amount of days gone by). Below is the sample Load script. Anyone have any idea what I'm doing wrong?

"T1":
LOAD * INLINE [
F1, F2
Event1, 01/06/2011
Event2, 07/06/2011
Event3, 18/07/2011
Event4, 05/08/2011
];

"T2":
Load F1, F2, Peek(F2) AS F3
Resident [T1];

"T3":
Load F1, F2, F3, Num((Date(F2, 'DD/MM/YYYY')- Date(F3, 'DD/MM/YYYY'))) AS [DaysPassed]
Resident T2;

Drop Table T1;
Drop Table T2;

Using peek and Date Calculations

johnw — Thu, 04 Aug 2011 23:05:07 GMT

Try date#() instead of date(). Date() is a formatting function. Date# converts a string into a date. Although as long as your dates match your default date format, I wouldn't think you'd need either function, because the fields should already be dates, I'd think. In any case, I suspect you're having formatting problems. One clue is whether F2 and F3 are left or right justified. If left, they probably didn't get interpreted as dates.

Using peek and Date Calculations

rahulgupta — Fri, 05 Aug 2011 05:02:46 GMT

Hey Hi,

Just some little changes:

LOAD * INLINE [
F1, F2
Event1, 01/06/2011
Event2, 07/06/2011
Event3, 18/07/2011
Event4, 05/08/2011
];

"T2":
Load F1, F2, Peek(F2) AS F3
Resident [T1];

Drop Table T1;

Load F1, F2, F3,

Num(Date(F2, 'DD/MM/YYYY'))- Num(Date(F3, 'DD/MM/YYYY')) AS [DaysPassed]
Resident T2;

Drop Table T2;

Hope this helps...

Regards

"T3":