Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

kkkumar82
Valued Contributor III

Count of rows on different date columns

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.

1 Solution

Accepted Solutions

Re: Count of rows on different date columns

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;

View solution in original post

5 Replies

Re: Count of rows on different date columns

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
Valued Contributor III

Re: Count of rows on different date columns

Hi Sunny,

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

Re: Count of rows on different date columns

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;

View solution in original post

rahulpawarb
Valued Contributor III

Re: Count of rows on different date columns

kkkumar82
Valued Contributor III

Re: Count of rows on different date columns

Thanks much Sunny,

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