6 Replies Latest reply: Feb 26, 2015 6:23 AM by Henric Cronström

# Effective From Date & Effective To date ?

HI All,

I am having a Final Table , I linked with Calendar on Date_Key Field..

I am having  one More Brand Table which is having Effective from date & Effective To date..

But I need to get a Single Date..

for Example in calendar I have selected Sep-2014, then I need to Show Effective from Date Only of September Month..

how can i bring into a Single date..

Can any one help me on this...

Thanks,

Helen

• ###### Re: Effective From Date & Effective To date ?

Use IntervalMatch function

• ###### Re: Effective From Date & Effective To date ?

If Select  Calendar Month, then Only Relevant Brand of that Particular months Should come...

Its not working I tried..

• ###### Re: Effective From Date & Effective To date ?

Hi Helen,

try below logic to populate missing dates and their respective amount:

TempBrand:
Load [Effective from date] , [Effective To date],Brand, Amount From Brand;

MinMaxDate:
Load Min([Effective from date]) as MinDate, Max([Effective To date]) as MaxDate resident TempBrand;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Join (TempBrand)
Load Date(recno()+\$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

Brand:
If( IsNull( Amount ), Peek( Amount ), Amount ) as Amount
Resident TempBrand
Order By Date ;
Drop Table MinMaxDate, TempBrand;

Regards

Neetha

• ###### Re: Effective From Date & Effective To date ?

Hi Helen,

I guess this a case of canonical date issue.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Regards

KC

• ###### Re: Effective From Date & Effective To date ?

Hi Helen,

Or try like this:

Events:
Load  Date_Key From Events;

Intervals:
Load [Effective from date],

[Effective To date]

From Intervals;

IntervalMatch:
IntervalMatch (Date_Key )
Load distinct [Effective from date], [Effective To date] resident Intervals;

Regards

KC

• ###### Re: Effective From Date & Effective To date ?

If you want to create all dates between FromDate and ToDate, you should look at Creating Reference Dates for Intervals

HIC