topic Re: Effective From Date & Effective To date ? in QlikView

Effective From Date & Effective To date ?

Thu, 26 Feb 2015 10:07:42 GMT

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 ?

MK_QSL — Thu, 26 Feb 2015 10:14:50 GMT

Use IntervalMatch function

Re: Effective From Date & Effective To date ?

Thu, 26 Feb 2015 11:01:28 GMT

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 ?

jyothish8807 — Thu, 26 Feb 2015 11:05:18 GMT

Hi Helen,

I guess this a case of canonical date issue.

Please go through this, may be help you.

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

Regards

Re: Effective From Date & Effective To date ?

jyothish8807 — Thu, 26 Feb 2015 11:09:00 GMT

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

Re: Effective From Date & Effective To date ?

Anonymous — Thu, 26 Feb 2015 11:14:35 GMT

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:
NoConcatenate Load Date,
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 ?

hic — Thu, 26 Feb 2015 11:23:51 GMT

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

HIC