topic Generation of missing dates across common axis for multiple date fields in QlikView

Generation of missing dates across common axis for multiple date fields

Anonymous — Tue, 14 Mar 2017 04:29:10 GMT

I am using a combination of Canonical Calendar and Generating missing reference dates concept .

we have multiple systems migrated into one new system and I need to show all dates on one time axis.

eg. [Opened Date], [Resolution Date] from New system

[Date Raised],[Resolved Date] from TR System

[Open date UTC],[Resolve date UTC] from IN system

I need to show a trend of outstanding tickets across one common month axis.

Outstanding ticket count as of 28 Feb = all tickets with blank [Resolution Date],[Resolved Date] and [Resolve date UTC] while being opened in Feb or prior to Feb. +

Tickets that were opened in Feb or prior to Feb but resolved after 28 Feb.

I am able to generate missing dates but only for one date field.

Challenge that I am facing is that even if I generate missing dates for each of these date fields how do i show it all on one axis.

Re: Generation of missing dates across common axis for multiple date fields

jonathandienst — Tue, 14 Mar 2017 05:05:03 GMT

If the Opened Date, Date Raised and Open date UTC all mean the same thing, then I would alias them so the name is the same:

LOAD

...

[Opened Date]

LOAD

...

[Date Raised] as [Opened Date],

etc

(This is assuming, of course, that you are concatenating the facts from all three sources into a single fact table)

Re: Generation of missing dates across common axis for multiple date fields

Anonymous — Wed, 15 Mar 2017 11:33:44 GMT

Thanks Jonathan.. This helped and it did work out though script takes a longer time to load