Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
rammuthiah
Not applicable

Interval Match

I have one data to find the Interval match : Can anyone help in this regard?

         

ItemOdateCrnyCost
115/1/2012EUR14
215/2/2012USD15
315/3/2012EUR16
415/8/2012USD

10

Another data

   

CrnyFromTo

rent

USD1/1/201230/4/20121.25
USD1/6/201230/12/20121.29
EUR1/1/201231/1/20121.23
EUR2/2/201230/4/20122.25

The result should be

 

ItemODateCrnyFromToCostrent
11/15/2012EUR1/1/20121/31/2012141.23
22/15/2012USD1/1/20124/30/2012151.25
33/15/2012EUR2/2/20124/30/2012162.25
48/15/2012USD6/1/201212/30/2012101.29
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Interval Match

Try this

Table1:

LOAD * INLINE [

    Item, Odate, Crny, Cost

    1, 15/1/2012, EUR, 14

    2, 15/2/2012, USD, 15

    3, 15/3/2012, EUR, 16

    4, 15/8/2012, USD, 10

];

Table2:

LOAD * INLINE [

    Crny, From, To, rent

    USD, 1/1/2012, 30/4/2012, 1.25

    USD, 1/6/2012, 30/12/2012, 1.29

    EUR, 1/1/2012, 31/1/2012, 1.23

    EUR, 2/2/2012, 30/4/2012, 2.25

];

Left Join (Table1)

IntervalMatch(Odate, Crny)

LOAD From,

  To,

  Crny

Resident Table2;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;

1 Reply
sunny_talwar
Not applicable

Re: Interval Match

Try this

Table1:

LOAD * INLINE [

    Item, Odate, Crny, Cost

    1, 15/1/2012, EUR, 14

    2, 15/2/2012, USD, 15

    3, 15/3/2012, EUR, 16

    4, 15/8/2012, USD, 10

];

Table2:

LOAD * INLINE [

    Crny, From, To, rent

    USD, 1/1/2012, 30/4/2012, 1.25

    USD, 1/6/2012, 30/12/2012, 1.29

    EUR, 1/1/2012, 31/1/2012, 1.23

    EUR, 2/2/2012, 30/4/2012, 2.25

];

Left Join (Table1)

IntervalMatch(Odate, Crny)

LOAD From,

  To,

  Crny

Resident Table2;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;