Adding a variable to a join based on Max Date

Anonymous — Wed, 07 Aug 2013 18:20:44 GMT

I have customers, which are booked for appointments. I use the following script to create a left join to see, when they're booked for their next appointment:

Left Join ([Appoint Temp])

LOAD Contact,

Date(Max(Left(EventDate,5))) as [NEXTTEVENT],

From [..\Data\QVD\xxx.qvd] (qvd)

Where Date(Left(EventDate,5))>Today()

Group By Contact;

That works well for me, and I get the result I'm looking for. My problem arises when I want to add a variable e.g. I want to see what kind of appointment type they've booked. The variable is called "Appointment Type" and I thought I could just add it to the script, but that doesn't work for me (I get an error saying invalid expression):

LOAD Contact,

Date(Max(Left(EventDate,5))) as [NEXTTEVENT],

AppointmentType as [NEXTAPPTTYPE]

From [..\Data\QVD\xxx.qvd] (qvd)

Where Date(Left(EventDate,5))>Today()

Group By Contact;

Will I need to do several loads?

Looking forward to hear from you

Re: Adding a variable to a join based on Max Date

Anonymous — Wed, 07 Aug 2013 18:54:08 GMT

Sorted it out with help from this discussion: http://community.qlik.com/message/318967#318967

Trick was to use FirstSortedValue

LOAD Contact,

Date(Max(Left(EventDate,5))) as [NEXTTEVENT],

FirstSortedValue(AppointmentType, EventDate) as [NEXTAPPTTYPE]

From [..\Data\QVD\xxx.qvd] (qvd)

Where Date(Left(EventDate,5))>Today()

Group By Contact;

topic Adding a variable to a join based on Max Date in QlikView

Adding a variable to a join based on Max Date

Re: Adding a variable to a join based on Max Date