Count of rows on different date columns

[kkkumar82]

Hi All,

I have a data set (in short) with a dimension and three date fields.

Now I have create a filter saying QuarterYear and when I select for one period for eg Q217 I want to see a table

with

Dimension, Count of rows regarding Date1, Count of Rows Regarding Date2, Count of rows regarding Date3

The Quarter Year should have association with all the three date column that is the trick I am missing here.

Attaching some sample data.

[sunny_talwar]

May be use a link table concept to do this where you do something like this

Fact:

LOAD Dimension,

     Date1,

     Date2,

     Date3

FROM ...;

LinkTable:

LOAD DISTINCT Date1 as MasterDate,

     Date1,

     'Date1' as DateFlag

Resident Fact;

Concatenate (LinkTable)

LOAD DISTINCT Date2 as MasterDate,

     Date2,

     'Date2' as DateFlag

Resident Fact;

Concatenate (LinkTable)

LOAD DISTINCT Date3 as MasterDate,

     Date3,

     'Date3' as DateFlag

Resident Fact;

Now create a master calendar for the MasterDate field....

[kkkumar82]

Hi Sunny,

Will the Link table won't create a synthetic key with the Fact table ?

[sunny_talwar · Accepted solution]

It will.... you can use a unique identifier from your fact table to avoid synthetic key

Fact:

LOAD UniqueIdentifier,

    Dimension,

    Date1,

    Date2,

    Date3

FROM ...;

LinkTable:

LOAD DISTINCT Date1 as MasterDate,

    UniqueIdentifier,

    'Date1' as DateFlag

Resident Fact;

Concatenate (LinkTable)

LOAD DISTINCT Date2 as MasterDate,

    UniqueIdentifier,

    'Date2' as DateFlag

Resident Fact;

Concatenate (LinkTable)

LOAD DISTINCT Date3 as MasterDate,

    UniqueIdentifier,

    'Date3' as DateFlag

Resident Fact;

Or create a new uniqueIndentifier using the three Dates

Fact:

LOAD AutoNumber(Date1&Date2&Date3) as UniqueIdentifier,

    Dimension,

    Date1,

    Date2,

    Date3

FROM ...;

LinkTable:

LOAD DISTINCT Date1 as MasterDate,

    UniqueIdentifier,

    'Date1' as DateFlag

Resident Fact;

Concatenate (LinkTable)

LOAD DISTINCT Date2 as MasterDate,

    UniqueIdentifier,

    'Date2' as DateFlag

Resident Fact;

Concatenate (LinkTable)

LOAD DISTINCT Date3 as MasterDate,

    UniqueIdentifier,

    'Date3' as DateFlag

Resident Fact;

[rahulpawarb]

May be this:

http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Regards!

Rahul

[kkkumar82]

Thanks much Sunny,

The second one worked for me with the sample I provided.