topic Re: Handle multiple Date field and Avoid Loop in QlikView

Handle multiple Date field and Avoid Loop

brijeshvma — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Handle multiple Date field and Avoid Loop

hic — Wed, 23 Mar 2016 07:47:15 GMT

You need a canonical date. See Canonical Date

HIC

Re: Handle multiple Date field and Avoid Loop

brijeshvma — Wed, 23 Mar 2016 08:11:07 GMT

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

Re: Handle multiple Date field and Avoid Loop

hic — Wed, 23 Mar 2016 09:22:18 GMT

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