topic Re: LOAD WITH 2 TABLES ? in QlikView

LOAD WITH 2 TABLES ?

Tue, 02 Jul 2013 09:22:51 GMT

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.

Re: LOAD WITH 2 TABLES ?

kaushiknsolanki — Tue, 02 Jul 2013 09:31:28 GMT

Hi,

Have a look at the below links for more help.

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

Regards,

Kaushik Solanki

Re: LOAD WITH 2 TABLES ?

Tue, 02 Jul 2013 09:48:46 GMT

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

;

Re: LOAD WITH 2 TABLES ?

er_mohit — Tue, 02 Jul 2013 10:10:47 GMT

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,

Resident PriceList;

Left join (Article)

LOAD Distinct* resident PriceList;

drop table PriceList;

hope it helps

Re: LOAD WITH 2 TABLES ?

Tue, 02 Jul 2013 10:16:24 GMT

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.

Re: LOAD WITH 2 TABLES ?

Tue, 02 Jul 2013 12:30:11 GMT

OK it's working