How to handle two date fields in Data model

[lakshman1031]

Hi All,

I have two tables, Summary table , and headcout overview

Summary table having Summary_Date and headcount overview having headcount_Date.

here the date field is converted as MMM-YYYY formatted.

My requirement is if i select date like Sep-2024 then it will reflect in both tables data.

Can you please suggest me how to create Master calendar using two date filed.

Thanks & Regards,

Lakshman  

[Qrishna]

Lets say you have date with format like below, you should be able to create a calndar :

Summary:
Load *,
Num(Date#(Contract_Date, 'DD-MMM-YYYY')) as %DateKey
;
LOAD * inline [
Contract_ID,Contract_Date, Contract_Value
1001,23-Jan-23,10000
1002,1-Feb-22,20000
1003,1-Mar-24,4622
];

Headcount:
Load *,
Num(Date#(JoinDate, 'DD-MMM-YYYY')) as %DateKey
;
LOAD * inline [
Customer,JoinDate
A,05-Jan-23
B,01-AUG-22
C,01-SEP-24
D,01-JUL-24
E,11-MAY-23
];

//----------------------------------------------------------------------------//
//Prepare Calendar from the above dates
Calendar_Temp:
Load Num(Date#(Contract_Date, 'DD-MMM-YYYY')) as Date
resident Summary;
Load Num(Date#(JoinDate, 'DD-MMM-YYYY')) as Date
resident Headcount;

MinMax:
LOAD
Min(Date) as MinDate,
Max(Date) as MaxDate
RESIDENT Calendar_Temp;

let vMinDate = num(peek('MinDate', 0, 'MinMax'));
let vMaxDate = num(peek('MaxDate', 0, 'MinMax'));

Calendar:
Load
Num(Date(DateNum, 'DD-MMM-YYYY')) as %DateKey,
Date#(Date(DateNum), 'DD-MMM-YYYY') as Date,
Month(DateNum) as Month,
Year(DateNum) as Year,
Month(DateNum) & '-' & Year(DateNum) as MonthYear;
Load distinct
$(vMinDate) + IterNo() - 1 as DateNum
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

drop table MinMax;
drop table Calendar_Temp;

There are several other ways to create a calendar, but this one should be able to give a basic idea. if the fact tables are a bit complex with different data granularities then the complexity of creating calendar also differs.

Check for my other solution: https://community.qlik.com/t5/App-Development/Issue-with-Multiple-Date-dimensions-How-to-handle-the-...

[lakshman1031]

Hi Qrishna,

Thank you for your replay.

I have two tables, Summary table , and headcout overview

Summary table having Summary_Date and headcount overview having headcount_Date.

In that Persion_ID is common filed based on the Persion_ID i provided joining.

Can you please suggest me how to handled this senario.

Thanks & Regards,

Lakshman

[Muthukumar_77 · Accepted solution]

Hi,

There are 2 ways,

Concatenate the both tables

or

Do Link table concept 

Concatenate:

SUMMARY:

Load Date,

Dim1,

    Dim2,

    Dim3,

    Dim5,

    Values

Resident Summary;

Concatenate(SUMMARY)

Load Date,

Dim1,

    Dim2,

    Dim3,

    Dim4,

    Headcount

Resident HeadCount;

Link Table: (If you have large volume data, better to create link table concept)

SUMMARY:

Load *, AutoNumber(Dim1 &'|'& Dim2 &'|'& Dim3,'_LINK_KEY') as _LINK_KEY;

Load '' as Date, //Common Fields from both tables

'' as Dim1, //Common Fields from both tables

    '' as Dim2,//Common Fields from both tables

    '' as Dim3,//Common Fields from both tables

    '' as Dim5,

    '' as Values

Inline [A];

HeadCount:

Load *, AutoNumber(Dim1 &'|'& Dim2 &'|'& Dim3,'_LINK_KEY') as _LINK_KEY;

Load '' as Date,//Common Fields from both tables

'' as Dim1,//Common Fields from both tables

    '' as Dim2,//Common Fields from both tables

    '' as Dim3,//Common Fields from both tables

    '' as Dim4,//Common Fields from both tables

    '' as Headcount

Inline [A];

//Create Link Table for Common Fields from both tables

NoConcatenate

Link_Table:

Load Distinct

_LINK_KEY,

    Date,

    Dim1,

    Dim2,

    Dim3

Resident SUMMARY;

Drop Fields Date,Dim1,Dim2,Dim3 From SUMMARY;

Concatenate(Link_Table)

Load Distinct

_LINK_KEY,

    Date,

    Dim1,

    Dim2,

    Dim3

Resident HeadCount;

Drop Fields Date,Dim1,Dim2,Dim3 From HeadCount;