topic Re: Interval Match Problems in QlikView

Interval Match Problems

anuradhaa — Wed, 25 Nov 2020 16:16:04 GMT

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 -

Re: Interval Match Problems

sunny_talwar — Tue, 03 May 2016 10:49:53 GMT

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;

Re: Interval Match Problems

anuradhaa — Tue, 03 May 2016 11:17:10 GMT

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

Re: Interval Match Problems

sunny_talwar — Tue, 03 May 2016 11:30:29 GMT

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;

Re: Interval Match Problems

anuradhaa — Tue, 03 May 2016 11:35:07 GMT

what about load * columns thing?

Re: Interval Match Problems

sunny_talwar — Tue, 03 May 2016 12:02:26 GMT

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

Re: Interval Match Problems

anuradhaa — Tue, 03 May 2016 12:04:44 GMT

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

Re: Interval Match Problems

sunny_talwar — Tue, 03 May 2016 12:16:15 GMT

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;

Re: Interval Match Problems

anuradhaa — Tue, 03 May 2016 12:23:01 GMT

I think you don't understand my question,

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

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;

Re: Interval Match Problems

sunny_talwar — Tue, 03 May 2016 12:26:07 GMT

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

Re: Interval Match Problems

anuradhaa — Tue, 03 May 2016 12:51:09 GMT

Table:

LOAD * Inline [

Name, Start, End ,country , flag

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

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

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

];

Final Table

Name Start End Date country flag

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

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

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

Re: Interval Match Problems

sunny_talwar — Tue, 03 May 2016 12:55:26 GMT

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