Discussion Board for collaboration on QlikView Scripting.
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...
Use IntervalMatch function
If Select Calendar Month, then Only Relevant Brand of that Particular months Should come...
Its not working I tried..
I guess this a case of canonical date issue.
Please go through this, may be help you.
Or try like this:
Events:Load Date_Key From Events;
Intervals:Load [Effective from date],
[Effective To date]
IntervalMatch:IntervalMatch (Date_Key )Load distinct [Effective from date], [Effective To date] resident Intervals;
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;
If you want to create all dates between FromDate and ToDate, you should look at Creating Reference Dates for Intervals