Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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?