Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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....
Hi Sunny,
Will the Link table won't create a synthetic key with the Fact table ?
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;
Thanks much Sunny,
The second one worked for me with the sample I provided.