Interval Match Problems

[anuradhaa]

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     -

[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]

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]

what about load * columns thing?

[sunny_talwar]

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

[anuradhaa]

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]

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?