Handle multiple Date field and Avoid Loop

[brijeshvma]

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  ...

[hic]

You need a canonical date. See Canonical Date

HIC

[brijeshvma]

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]

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