topic Re: Organise Data Within Load For Calendar in QlikView

Organise Data Within Load For Calendar

Mon, 11 Mar 2013 13:07:44 GMT

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

Re: Organise Data Within Load For Calendar

Gysbert_Wassenaar — Mon, 11 Mar 2013 13:24:43 GMT

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;

Re: Organise Data Within Load For Calendar

Mon, 11 Mar 2013 13:56:00 GMT

Thats great. Thankyou for your help

Re: Organise Data Within Load For Calendar

Mon, 11 Mar 2013 14:08:01 GMT

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.