Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
anuradhaa
Partner - Creator II
Partner - Creator II

Interval Match Problems

I have two tables and want to use interval match to map records,

Table 1 : (Base table)

Name     Start     End

aaa     2015/03/14     2015/05/25

aaa     2016/01/12     2016/05/20

ABC     2016/02/12     2016/04/30

Table2

Name          Date

aaa               2016/02/14

aaa               2016/03/12

aaa                2015/04/14

I want to put largest date of each cycle (Start -> End) is date falls in date range Start - > End

Final Table

Name     Start     End      Date

aaa     2015/03/14     2015/05/25     2015/04/14

aaa     2016/01/12     2016/05/20     2016/03/12

ABC     2016/02/12     2016/04/30     -

1 Solution

Accepted Solutions
sunny_talwar

Check out attached, you don't have to add all the names and it should still work for you

View solution in original post

11 Replies
sunny_talwar

May be this:

Table:

LOAD * Inline [

Name, Start, End

aaa, 2015/03/14, 2015/05/25

aaa, 2016/01/12, 2016/05/20

ABC, 2016/02/12, 2016/04/30

];

Table2:

LOAD * Inline [

Name, Date

aaa, 2016/02/14

aaa, 2016/03/12

aaa, 2015/04/14

];

Left Join (Table)

IntervalMatch(Date, Name)

LOAD Start,

  End,

  Name

Resident Table;

Left Join (Table)

LOAD *

Resident Table2;

DROP Table Table2;

Right Join (Table)

LOAD Name,

  Start,

  Date(Max(Date)) as Date

Resident Table

Group By Start, Name;

anuradhaa
Partner - Creator II
Partner - Creator II
Author

Hi Sunny,

I have two questions

1. End Date is missing for Name='ABC'

2. when there are too may columns in table how can i modify that with '*'.

i used

Left Join (Table)

IntervalMatch(Date, Name)

LOAD Start,

  End,

  Name

Resident Table;

Left Join (Table)

LOAD *

Resident Table2;

DROP Table Table2;

Right Join (Table)

LOAD *,

  Date(Max(Date)) as Date

Resident Table

Group By Start, Name;

But it gives errors

sunny_talwar

Here you go:

Table:

LOAD * Inline [

Name, Start, End

aaa, 2015/03/14, 2015/05/25

aaa, 2016/01/12, 2016/05/20

ABC, 2016/02/12, 2016/04/30

];

Table2:

LOAD * Inline [

Name, Date

aaa, 2016/02/14

aaa, 2016/03/12

aaa, 2015/04/14

];

Left Join (Table)

IntervalMatch(Date, Name)

LOAD Start,

  End,

  Name

Resident Table;

Left Join (Table)

LOAD *

Resident Table2;

DROP Table Table2;

Right Join (Table)

LOAD Name,

  Start,

  End,

  Date(Max(Date)) as Date

Resident Table

Group By Start, Name, End;

anuradhaa
Partner - Creator II
Partner - Creator II
Author

what about load * columns thing?

sunny_talwar

LOAD * will also include Date and we don't want to join on Date

anuradhaa
Partner - Creator II
Partner - Creator II
Author

i mean if i have several columns in Table1

how can i modify the script to load all columns in Table1 with out adding all column names in script

sunny_talwar

I guess the issue is the Null that we are generating in Date. If that is going to happen, you might need some extra resident loads. Try this:

Table:

LOAD * Inline [

Name, Start, End

aaa, 2015/03/14, 2015/05/25

aaa, 2016/01/12, 2016/05/20

ABC, 2016/02/12, 2016/04/30

];

Table2:

LOAD * Inline [

Name, Date

aaa, 2016/02/14

aaa, 2016/03/12

aaa, 2015/04/14

];

Left Join (Table)

IntervalMatch(Date, Name)

LOAD Start,

  End,

  Name

Resident Table;

Left Join (Table)

LOAD *

Resident Table2;

FinalTable:

NoConcatenate

LOAD *,

  Alt(Date, 0) as Date1

Resident Table;

Right Join (FinalTable)

LOAD Name,

  Start,

  Date(Max(Date1)) as Date1

Resident FinalTable

Group By Start, Name;

FinalFinalTable:

NoConcatenate

LOAD Name,

  Start,

  End,

  If(Date1 > 0, Date1) as Date

Resident FinalTable;

DROP Table Table2, Table, FinalTable;

anuradhaa
Partner - Creator II
Partner - Creator II
Author

I think you don't understand my question,

In my actual case i have multiple columns in Table instead of Name, Start, End

like

Table:

LOAD * Inline [

Name, Start, End,dedcc,edefddd,rfrfr ........

aaa, 2015/03/14, 2015/05/25

aaa, 2016/01/12, 2016/05/20

ABC, 2016/02/12, 2016/04/30

];


So when loading the script i want want to write all column names and is it possible to include

Load * resident Table;



sunny_talwar

I guess would you be able to add few of those columns to show how they are going to look?