Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have three date fields like Year,StartDate,EndDate.
Year calendar year which is used as a Filter.I have also few other fields like Agent and Sales and Sale year.
Now my requirement is like when i select a year in filter it should fall in between start and end dates of that selected agent and i have to show the data which is from the selected year to end year of that particular agent.
for ex.agent a have start date like 01/01/2012 and end date is like 10/16/2014.first i will select an agent and after that,
if i select year in filter it should fall between 01/01/2012 and 10/06/2014.
and the data which have to display should be from selected year to end year.
If i select 2013 in Year filter,The agent StartDate is 2012 and EndDate is 2014.I want to Display the agent sales from selected year 2013 to end year 2014.If the selected year is not in between the startDate and EndDate like 2011 then we should not show any data .
Tab1
LOAD * INLINE [
ID, StartYear, EndYear
1, 2011, 2015
2, 2010, 2014
3, 2009, 2013
4, 2008, 2011
5, 2012, 2014
6, 2014, 2015
7, 2007, 2010
8, 2006, 2014
9,2003, 2015
10, 2005, 2010
11, 2009, 2013
];
Tab2:
Load * Inline
[
ID,Sales,SaleYear
1,100,2011
1,150,2012
1,200,2013
1,250,2014
2,120,2012
3,130,2013
2,150,2014
2,190,2015
2,200,2013
3,180,2014
3,300,2015
];
LOAD StartYear as Year
Resident Tab1;
Please Help me It is an urgent requirement.I am unable to do this.
Hi,
Try using interval match function in qv.
HTH
sushil
Hi Sushil kumar,
Please give me the Sample expression for this issue using intervalmatch
Hi
Try like this
Tab1:
Load * Inline
[
ID,Sales,SaleYear
1,100,2011
1,150,2012
1,200,2013
1,250,2014
2,120,2012
3,130,2013
2,150,2014
2,190,2015
2,200,2013
3,180,2014
3,300,2015
];
Tab2:
LOAD * INLINE
[
ID, StartYear, EndYear
1, 2011, 2015
2, 2010, 2014
3, 2009, 2013
4, 2008, 2011
5, 2012, 2014
6, 2014, 2015
7, 2007, 2010
8, 2006, 2014
9,2003, 2015
10, 2005, 2010
11, 2009, 2013
];
Inner Join
IntervalMatch(SaleYear, ID)
LOAD StartYear, EndYear, ID Resident Tab2;
Join(Tab1)
LOAD * Resident Tab2;
DROP Table Tab2;