Organise Data Within Load For Calendar

[#1]

Hi,

I currently have a dataset that I want to convert into a visual calendar within QlikView.  I beleive the best way to do this would be to set it up within a pivot table.  The problem that I have is the data itself and how to go about combining certain fields based upon the date and time.

Example Raw Table:

Key	Date	Day	Action	Time
1	11/03/2013	Mon	Visit	11am
2	12/03/2013	Tue	Visit	10am
3	13/03/2013	Wed	Visit	2pm
4	14/03/2013	Thur	Visit	3pm
5	12/03/2013	Tue	Call	2pm
6	12/03/2013	Tue	Call	4pm
7	14/03/2013	Thur	Call	10am

What I want to get to:

Date	Day	Detail
11/03/2013	Mon	Visit @ 11am
12/03/2013	Tue	Visit @10am  Call @ 2pm  Call @ 4pm
13/03/2013	Wed	Visit @ 2pm
14/03/2013	Thur	Call @ 10am   Visit @ 3pm

I have no idea how to do a field merge based upon a lookup.

Can anyone help point me in the right direction?

Many thanks

[Gysbert_Wassenaar · Accepted solution]

Something like: concat( Action & ' @ ' & Time, '   ', Key )

See attached qvw

If you want to calculate this in the script use something like:

load Date, concat( Action & ' @ ' & Time, '   ', Key ) as Actions

from ...somewhere...

group by Date;

[#3]

Thats great.  Thankyou for your help

[#4]

Is it possible to use a character return on order to split up the date within the field?

I have tried

chr(13)

this does not appear to work.