Need help with grouping

[diffeyj]

Hi All,

I have what I thought was a simple problem that has turned out to be a little trickier than expected and was hoping someone could assist. Basically I have a database of cars and them being hired out to "clients". The database consists of 2 tables of relevance.

1. The table of cars which includes its current location and a tonne of fields that are not required for this example.

Car ID	Current Location
car1	base
car2	x
car3	y

2. A table of "events" on the car, including the location change.

Car ID	LocationFrom	LocationTo	EffectiveDate
car1	base	x	02/01/2013
car1	x	y	07/03/2013
car1	y	base	22/03/2013
car2	base	x	02/01/2013
car2	x	base	09/02/2013
car3	base	y	05/03/2013
car3	y	base	07/03/2013
car4	base	x	22/01/2013
car4	x	z	27/01/2013
car4	z	base	28/01/2013

I want a chart (line graph or bar chart, not really fussed at this stage) that shows me month by month, how many cars were being "used" at the end of the month. The chart should show 3 months in this example as there is only 3 months worth of data. The numbers should be

Jan = 3 (car4 not included as it was back at base before the end of the month, the other 3 were out at clients)

Feb = 1 (car1 was the only car out at clients)

Mar = 0 (car1 was returned during the month)

I can't work out how to get the above results in qlikview though. A couple of issues being that in the above example car1 did not have an "event" in feb, it was at a client for all of feb.

I hope this makes sense, any help would be greatly appreciated

[diffeyj]

on reflection I believe the 1st table in the above example is not relevent to this example.

added a very simple qlikview file with the inline statement done if it is useful for anyone

[#3]

Hi,

have you tried something like:

count(if(LocationTo=base;0;1))  ?

This should give you a count of all the lines where the "LocationTo" is not equal base. If I understood your problem, that's what you want?

Greeting Wiley_E

[#4]

oh... correction:

I just recognized, that my last answer would give you counts for the movements during the months....

maybe you could combine the Count-statement above with a search for the "maximum" date stamp per month....

Greetings Wiley_E

[diffeyj]

Hi Wiley_e_

thanx for the response, unfortunately the problem is that using the above example neither of these options would tell me that car1 was being used for March as it was out for over a month and there were no "events" in march

[rwunderlich]

Attached is one possible solution.

The first step is to expand the events table into a row for every date in the interval.  Henric;s blog post

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

describes this in detail. For this example, I used a QVC (http://qlikviewcomponents.org) routine to expand the table:

  CALL Qvc.ExpandInterval('CarEvents', 'EffectiveDate', '', '[Car ID]');

The next step is to flag the month end dates in the table. I chose to create a new field for those rows that have a month end date:

Right JOIN (CarEvents)

LOAD Distinct *

          ,if(EffectiveDate = floor(MonthEnd(EffectiveDate)), date(EffectiveDate,'YYYY-MMM')) as MonthEnd

RESIDENT CarEvents

;

At this point, you could summarize the <>base [Car ID] count in the script. I chose to do the counting in the chart, anticipating that you may want to use additonal selectons like "Make=Toyota".  The chart Dimension is the previously created "MonthEnd" field , the expression is:

=sum(0) +

count({<LocationTo=-{"base"}>}[Car ID])

The sum(0) bit is a trick to force inclusion of the months that have zero cars.

-Rob

http://masterssummit.com

http://robwunderlich.com