Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to count event between 2 dates : SELECT count(*) from Calendar, Events WHERE YearWeekStartE <= YearWeekC and YearWeekEndE > YearWeekC
TABLE CALENDAR
YearWeekC
and
TABLE EVENTS
YearWeekStartE
YearWeekEndE
How to do it in QlikView ?
Thanks a lot.
Hi,
Have a look at the below links for more help.
http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch
Regards,
Kaushik Solanki
Hi,
Have a look at the below links for more help.
http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch
Regards,
Kaushik Solanki
Thanks Kaushik,
I've tried your solution, but at the moment I've a Syn_TABLE :
I dont know why ?
INTERVAL:
IntervalMatch ([(D)Date])
LOAD Distinct [(DS)DATE_DEBUT_SUSPENSION],[(DS)DATE_FIN_SUSPENSION]
Resident TMP_DEMANDE_SUSPENSION
;
you can do this using interval match
see the below code copy and run it in your script
Article:
LOAD date(Date#(Purchase_date,'DD/MM/YYYY'),'DD/MM/YYYY') AS PD,* INLINE [
Article, Purchase_date
A1, 01/12/2012
A2, 15/08/2012
A3, 01/01/2013
A4, 03/03/2013
A5, 21/04/2013
];
PriceList:
LOAD date(Date#(Start_Date,'DD/MM/YYYY'),'DD/MM/YYYY') AS SD,
date(Date#(End_Date,'DD/MM/YYYY'),'DD/MM/YYYY') AS ED,
* INLINE [
Article, Price, Start_Date, End_Date
A1, 5, 01/01/2012, 31/12/2102
A1, 7, 01/01/2013, 31/12/2013
A2, 10, 01/01/2012, 30/06/2012
A2, 8, 01/07/2012, 31/12/2103
A3, 2, 01/01/2013, 31/12/2013
A4, 12, 01/01/2013, 15/02/2103
A4, 13, 16/02/2013, 28/02/2103
A4, 15, 01/03/2103, 15/03/2013
];
Price:
Left Join (Article)
IntervalMatch(PD)
LOAD SD,
ED
Resident PriceList;
Left join (Article)
LOAD Distinct* resident PriceList;
drop table PriceList;
hope it helps
Hi,
Thanks a lot for your answer.
I've tried this method (I have to check if it's correct but seems OK) :
Tmp_BridgeTable:
Load distinct EventDate Resident Events ;
Join
Load IntervalID, IntervalBegin, IntervalEnd Resident Intervals ;
BridgeTable:
Load distinct EventDate, IntervalID
Resident Tmp_BridgeTable
Where IntervalBegin <= EventDate and EventDate < IntervalEnd ;
Drop Table Tmp_BridgeTable ;
I will come back to inform you.
OK it's working