Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

LOAD WITH 2 TABLES ?

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.

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Hi,

     Have a look at the below links for more help.

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Hi,

     Have a look at the below links for more help.

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Highlighted
Not applicable

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

;

Highlighted
Master II
Master II

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

Highlighted
Not applicable

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.

Highlighted
Not applicable

OK it's working