LOAD WITH 2 TABLES ?

[#1]

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.

[kaushiknsolanki · Accepted solution]

Hi,

     Have a look at the below links for more help.

     http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Regards,

Kaushik Solanki

[#3]

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

;

[er_mohit]

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

[#5]

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.

[#6]

OK it's working