Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
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;
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;