Expanding date fields

Thu, 12 Nov 2009 00:41:58 GMT

One of my co workers posted a post earlier concerning something similiar and though the theory worked it will not work for what we need it for.

Here is the situation:

We have two tables:

The First Contains Dispatch Info with a new entry put in every time the driver switches loads:

TripNo	TrailerNo	Dispatch Date	DriverCode	Skirts
51455	4587	11/1/2009	DIRKA	0
51465	4547	11/5/2009	DIRKA	0
51568	4578	11/9/2009	DIRKA	1
58465	4578	11/14/2009	DIRKA	0
54686	4685	11/10/2009	MARKC	0
51548	4578	11/15/2009	MARKC	1

The 2nd contains MPG information Date Ranges Differ from table above the MPG query is trigger manually or by a new dispatch:

StartDate	EndDate	DriverCode	MPG
11/1/2009	11/3/2009	DIRKA	12.5
11/3/2009	11/5/2009	DIRKA	11.5
11/5/2009	11/9/2009	DIRKA	14.5
11/9/2009	11/14/2009	DIRKA	16.5
11/14/2009	11/16/2009	DIRKA	10.3
11/10/2009	11/12/2009	MARKC	11.5
11/12/2009	11/15/2009	MARKC	13.2
11/15/2009	11/16/2009	MARKC	17.2

My idea is converting the first table into something like this:

TripNo	TrailerNo	Dispatch Date	DriverCode
51455	4587	11/1/2009	DIRKA
51455	4587	11/2/2009	DIRKA
51455	4587	11/3/2009	DIRKA
51455	4587	11/4/2009	DIRKA
51465	4547	11/5/2009	DIRKA
51465	4547	11/6/2009	DIRKA
51465	4547	11/7/2009	DIRKA
51465	4547	11/8/2009	DIRKA
51568	4568	11/9/2009	DIRKA
51568	4568	11/10/2009	DIRKA
51568	4568	11/11/2009	DIRKA
51568	4568	11/12/2009	DIRKA
51568	4568	11/13/2009	DIRKA
58465	4568	11/14/2009	DIRKA
58465	4568	11/15/2009	DIRKA
58465	4568	11/16/2009	DIRKA
54686	4685	11/10/2009	MARKC
54686	4685	11/11/2009	MARKC
54686	4685	11/12/2009	MARKC
54686	4685	11/13/2009	MARKC
54686	4685	11/14/2009	MARKC
51548	4568	11/15/2009	MARKC
51548	4568	11/16/2009	MARKC

Thus giving each date a full row of info. Then making a "=Date&DriverCode" key.

Then do the same with the MPG Table and make another "Date&DriverCode" Key

We do not know how to do this expansion inside the code. I have wondered if an autogenerate macro would do it. AutoGenerate(date) until a new record #?

I dont know just through things out there.

The end goal is to be able to track the MPG for each driver each day and compare those days he tows trailers with "skirts" to the days he tows other trailers.

Any ideas are welcome.

Please Help.

Expanding date fields

johnw — Thu, 12 Nov 2009 05:16:00 GMT

If this is long haul trucking data, having multiple dispatches in a single day seems like an inevitability. When that does happen, merely storing dates is ambiguous. You say you trigger a new MPG record for each dispatch. So now we have multiple MPG records and multiple dispatches, all on the same day, with nothing to distinguish between them. Multiple dispatches in a single day is also a problem for your intended goal of one record per day. You would need multiple records per day.

The simple solution to the ambiguity problem is to store timestamps, not merely dates. However, it will not be possible to create a separate record for every millisecond in the day. Perhaps it is impossible to have more than one dispatch in an hour, and you could generate hourly records, but that's still a whole lot of redundant data.

So I think we need to get back to this:

The end goal is to be able to track the MPG for each driver each day and compare those days he tows trailers with "skirts" to the days he tows other trailers.

See the attached for a way to turn your raw data into a single combined table with records that support this analysis. If I've misunderstood the requirement, please clarify.

I should also note that your example data is insufficient to actually calculate the average MPG. I weighted it by days driven, since that seemed to be what you were after, but this is incorrect. The correct way to handle it is to store both the miles driven and the gallons of gasoline used, then simply sum(Miles)/sum(Gallons). Is this information available?

Mind you, I really need to get back to my own work, so I'm not sure when or if I might be able to get back to this. Ah, well. Hopefully something I've said was helpful.

topic Expanding date fields in QlikView

Expanding date fields

Expanding date fields