Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Intervalmatch or Map - Please Help

Hi,

I'm trying to link the following tables, but I need to look to the dates as well as the main link.

main:                                          

LinkStart DateEnd Date
AA20/01/200919/01/2010
AA20/01/201019/01/2011
BB05/03/201206/03/2013
CC10/04/201009/04/2011
CC10/04/201109/04/2012
CC10/04/201209/04/2013
DD06/06/201105/06/2012
DD06/06/201205/06/2013
EE09/09/201108/09/2012

Other:

LinkMatch Dateclaim
AA04/05/2009123
BB18/06/2012111
CC20/06/2011234
EE02/01/2012344

Ideally I would like to map the Start Date from the 'main' table to the 'Other' table. As you can see there can be multiple start and end dates attached to each 'Link', I only want to map the dates that the 'Match Date' falls between.

***

e.g.(only looking at AA)

LinkStart DateEnd Date
AA20/01/200919/01/2010
AA20/01/201019/01/2011

LinkMatch Dateclaim
AA04/05/2009123

The Match date falls between 20/01/2009 - 19/01/2010 , therefore this is the start date that should be mapped onto the 'Other' table.

***

The end result should look like this:

LinkMatch DateStart Date
AA04/05/200920/01/2009
BB18/06/201205/03/2012
CC20/06/201110/04/2011
EE02/01/201209/09/2011

I have tried using Intervalmatch, but this only seems to look to the dates and doesn't look to the Link.

example is attached.

I would be grateful for your help.....

Tags (3)
1 Solution

Accepted Solutions
rsvebeck
Valued Contributor

Re: Intervalmatch or Map - Please Help

But it should be possible to build a combined key with dates and ID, and do interval match on this instead of just justing dates for interval match. Perhaps the ID needs to be numeric, can you use autonumberhas128 for the ID part and combine it with your dates? Please upload you excel file also if you need more help. // Robert

7 Replies
Not applicable

Re: Intervalmatch or Map - Please Help

So no one can help?

rsvebeck
Valued Contributor

Re: Intervalmatch or Map - Please Help

Perhaps this will work:

main:

LOAD Link,
[Start Date],

    
[End Date],

    
Link & [Start Date] as StartKey,

    
Link & [End Date] as EndKey

FROM



(
biff, embedded labels, table is Sheet1$);









Other:

LOAD Link,

    
[Match Date],

    
Link & [Match Date] as Key

FROM



(
biff, embedded labels, table is Sheet2$);







INNER JOIN (Other) INTERVALMATCH (Key) LOAD StartKey, EndKey RESIDENT main;

LEFT JOIN (Other) LOAD * RESIDENT main;

DROP TABLE main;

Not applicable

Re: Intervalmatch or Map - Please Help

Hi Robert,

Thank you for replying, but this hasn't worked!

looks like I'm going to have to think of a completely different approach to this dilemma..

rsvebeck
Valued Contributor

Re: Intervalmatch or Map - Please Help

But it should be possible to build a combined key with dates and ID, and do interval match on this instead of just justing dates for interval match. Perhaps the ID needs to be numeric, can you use autonumberhas128 for the ID part and combine it with your dates? Please upload you excel file also if you need more help. // Robert

Not applicable

Re: Intervalmatch or Map - Please Help

Hi,

try this it may help you.

Thanks,

Niranjan M.

Not applicable

Re: Intervalmatch or Map - Please Help

Hi Robert,

Thank you so much!!! this was my first time using autonumber and combined with your earlier suggestion; it seems to have done the trick!!

Here is how I did it:

main:
LOAD Link as Link1,
     AutoNumber(Link) as autolink,
     [Start Date],
     [End Date]
FROM

(biff, embedded labels, table is Sheet1$);

Other:
LOAD Link as Link2,
     AutoNumber(Link) as autolink,
     [Match Date]
FROM

(biff, embedded labels, table is Sheet2$);


main1:
LOAD Link1 as LinkA,
     autolink,
     [Start Date],
     [End Date],
     autolink & [Start Date] as StartKey,
     autolink & [End Date] as EndKey
resident main;
drop table main;


Other1:
LOAD Link2 as LinkB,
     autolink,
     [Match Date],
     autolink & [Match Date] as Key
resident Other;
drop table Other;


INNER JOIN (Other1) INTERVALMATCH (Key) LOAD StartKey, EndKey RESIDENT main1;

LEFT JOIN (Other1) LOAD * RESIDENT main1;

DROP TABLE main1;

Niranjan - this didn't work for what I wanted, but thank you for your suggestion


Not applicable

Re: Intervalmatch or Map - Please Help

Hi,

we can try above login by extended interval match. see below

Main:

Load

Link,

Date#(StartDate,'dd/mm/yyyy') As StartDate,

Date#(EndDate,'dd/mm/yyyy') As EndDate

;

LOAD * INLINE [

    Link, StartDate, EndDate

    AA, 20/01/2009, 19/01/2010

    AA, 20/01/2010, 19/01/2011

    BB, 05/03/2012, 06/03/2013

    CC, 10/04/2010, 09/04/2011

    CC, 10/04/2011, 09/04/2012

    CC, 10/04/2012, 09/04/2013

    DD, 06/06/2011, 05/06/2012

    DD, 06/06/2012, 05/06/2013

    EE, 09/09/2011, 08/09/2012

]
;



Other:

Load

Link as Link,

Date#(MatchDate,'dd/mm/yyyy') As MatchDate,

claim

;

LOAD * INLINE [

Link,     MatchDate,     claim

AA,     21/01/2009,     123

BB,     18/06/2012,     111

CC,     20/06/2011,     234

]
;



Inner Join(Other) IntervalMatch(MatchDate,Link) LOAD StartDate,EndDate,Link Resident Main;

Community Browser