Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
brijeshvma
Partner - Creator
Partner - Creator

Handle multiple Date field and Avoid Loop

Hi All ,

I have A two table Leads and Session.

In Lead Table Column are like LeadId , LeadName , ChannelID, LeadDate

And Session table SessionID, SessioName , ChannelID , SessionDate ..

So Both the table are Connected with ChannelID..  and we have to map this two table with ChannelID,

i am as well creating a Master calander with Date , SessionDate, LeadDate and rest of date Dimension..

So its creating a Loop Between Then ..as Lead is Connected with Session, Session connected with Master Calander and

Master calander as well connected with Lead table..

Kinldy Help me resolve this  ...

3 Replies
hic
Former Employee
Former Employee

You need a canonical date. See Canonical Date

HIC

brijeshvma
Partner - Creator
Partner - Creator
Author

Hi HIC,

Thanks for ur Reply, I am not able to understand how DateBridge is Created.. Can u suggest me how to create in my Case..

hic
Former Employee
Former Employee

It depends on your data.

If you have a many-to-many relationship between Leads and Sessions, you may need to join the two tables:

tmpCombinations:

Load * From Leads ;

Join Load * From Sessions ;

Combinations:

Load *, Autonumber(LeadId &'|'& SessionID) as CominationID Resident tmpCombinations;

Drop Table tmpCombinations;

DateBridge:

Load distinct SessionDate as Date, 'SessionDate' as Type, CominationID Resident Combinations ;

Load distinct LeadDate as Date, 'LeadDate' as Type, CominationID Resident Combinations ;

MasterCalendar:

Load distinct

   Date,

   Date(MonthStart(Date),'YYYY-MMM') as YearMonth

   Resident DateBridge;

If you have a one-to-many relationship between Leads and Sessions (a lead can have several sessions, but a session has only one lead) then there are simpler solutions.

HIC