topic Re: DateDiff in WHERE clause with aggregate function in QlikView

DateDiff in WHERE clause with aggregate function

Anonymous — Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

I need to find which customers came back within 30 days of departure. I have the below script but datediff doesn't work in WHERE clause. What's the best to do this in QlikView? If you could create a similar sample, it will greatly help.

select OverallNumber,VisitID,coalesce(CheckInDateTime,ServiceDateTime) as ArrivalDateTime, DepartDateTime from Visits where OverallNumber='100' and DepartDateTime is not null and coalesce(CheckInDateTime,ServiceDateTime) is not null --and DATEDIFF(day,MIN(coalesce(CheckInDateTime,ServiceDateTime)), Max(DepartDateTime))<30 order by ArrivalDateTime;

Current data:

OverallNumber VisitID ArrivalDateTime DepartDateTime 100 1 6/13/2015 6/15/2015 100 2 11/5/2015 11/7/2015 100 3 11/20/2015 11/25/2015

Desired result:

OverallNumber VisitID ArrivalDateTime DepartDateTime VisitID ArrivalDateTime DepartDateTime 100 2 11/5/2015 11/7/2015 3 11/20/2015 11/25/2015

Thanks a lot.

Re: DateDiff in WHERE clause with aggregate function

maxgro — Tue, 10 Nov 2015 22:25:43 GMT

zz:

load * inline [

OverallNumber VisitID ArrivalDateTime DepartDateTime

100 1 6/13/2015 6/15/2015

100 2 11/5/2015 11/7/2015

100 3 11/20/2015 11/25/2015

] (delimiter is spaces);

zzz:

load

*, ADT - DepartDateTime as Diff;

load

Peek('ArrivalDateTime') as ADT,

Peek('DepartDateTime') as DDT,

Peek('VisitID') as VID

Resident zz

Order By OverallNumber, VisitID desc;

DROP Table zz;

Re: DateDiff in WHERE clause with aggregate function

Anonymous — Tue, 10 Nov 2015 23:14:59 GMT

Wow! great. I'll try your code tomorrow and update you. May be others might come up with different ways as well. Thanks a lot.

Re: DateDiff in WHERE clause with aggregate function

Anonymous — Sat, 14 Nov 2015 05:27:12 GMT

Hi Maxgro!

This works when customer comes back twice within a month. However, if the customer came back 3 or 4 times in a month, then this code doesn't display the data since I will have to pick specific date to display the data. What do you think can solve this issue to display if there are more than 3 visits in a month. It will be fine if it gets on the second row. Thanks.

Re: DateDiff in WHERE clause with aggregate function

maxgro — Sat, 14 Nov 2015 14:16:13 GMT

My answer doesn't depends on the number of visit in a month; it just calculates the date difference between depart and next arrival.

Maybe I don't understand; post a new example with source data and expected result.

I suggest to post this new question in a new post (with a link to this) becuase usually people in the community look at non answered question.

Re: DateDiff in WHERE clause with aggregate function

Anonymous — Sun, 15 Nov 2015 00:28:00 GMT

I understand. I opened a new thread(title below) if you could assist on that when you get time. It looks like a simple fix since results works fine when I use a table box. The issue is with the straight table, which I am willing to use.Thank you.

Straight table doesn't display values like Table box

Re: DateDiff in WHERE clause with aggregate function

Anonymous — Sun, 15 Nov 2015 00:41:50 GMT

I got it working. Initially, I was including on OverallID in the dimension but I added VisitID in the dimension and that fixed it. OverallID remains the same for the visit but VisitID changes so that did the tricked. Just updating this for others. Thanks for your time.