Iterno and Gannt Chart Question

[#1]

Hello all,

I need to have a "Gannt Chart" for a QV file.

I have data as

ID	Date	Arr	Dep
101	01.04.2012	City1	City2
101	01.04.2012	City2	City1
101	02.04.2012	City1	City3
101	04.04.2012	City3	City1
101	07.04.2012	City1	City4
101	09.04.2012	City4	City1
101	13.04.2012	City1	City5
101	13.04.2012	City5	City1

City 1 is where the company operates, base city..I need to make a table like below.

	April
	1	2	3	4	5	6	7	8	9	10	11	12	13
101	City2	City3	City3	City3			City4	City4	City4				City5

For example for the 1st of April, the person goes to City2 and comes back to City1 on the same day,so I should make 1st of April green on Gannt Chart,but like at 2nd of April, he goes to City3 and comes back at 4th of April, and I should make the Gannt chart green for 2nd,3rd, and 4th of April. I know that I should make the load statement with "Iterno()", but how do I say "Check the Departure, if it is City 1 then take it"

thanks in advance

Seyma

[swuehl · Accepted solution]

Seyma,

I think you can do it like this:

INPUT:

LOAD recno() as Line,

     if(Arr<>'City1', peek(Date)) as TripStart,

     if(Arr<>'City1',Date) as TripEnd,

     if(Arr<>'City1', Arr) as  TripTarget,

     ID,

     Date,

     Arr,

     Dep

FROM

[http://community.qlik.com/thread/54680?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

LOAD autonumber(Line) as TripNo, ID, TripTarget, Date(TripStart+iterno()-1) as TripDate

Resident INPUT while TripStart+iterno()-1 <= TripEnd;

drop table INPUT;

In the INPUT table, I created new fields TripStart, TripEnd and TripTarget from your data for the 'returning' record lines (Arr<>'City1'). Then I used a while loop in a second load to create all trip dates with appropriate data (here I used your requested iterno() ).

The results table should already give you the requested information. If you want, you can link the TripDate to a master calendar, so you can create a calendar style object also with days without trips. Search the forum for master calendar if you want to.

Regards,

Stefan

[#3]

Thank you Swuehl, it works