DateDiff in WHERE clause with aggregate function

[Anonymous]

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.

[maxgro · Accepted solution]

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;

[Anonymous]

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.

[Anonymous]

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.

[maxgro]

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.

[Anonymous]

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

[Anonymous]

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.