Finding a date range within a script

[melissapluke]

Hi all,

I am trying to accomplish the below issue within the load script. I am able to doing this using the FirstSortedValue function within a chart in the UI, but am not able to figure it out within a script.  I have the following situation:

Table A:

Key          DateStart          DateEnd          Rate

ABCD     1/1/2016            1/31/2016        $10

ABCD     1/1/2017            1/31/2017        $8

WXYZ     1/1/2016            1/31/2016        $11

WXYZ     1/1/2017            1/31/2017        $12

Table B:

ID          Key     Date

1          ABCD     7/15/2016

2          ABCD     12/30/2017

3          ABCD     1/1/2018

4          WXYZ     2/2/2016

I need return the appropriate rate for each ID.

This is the table I am expecting.

Table B:

ID          Key     Date                    Rate

1          ABCD     7/15/2016          $10

2          ABCD     12/30/2017        $8

3          ABCD     1/1/2018              -

4          WXYZ     2/2/2016          $11

I've tried using applymap, but because of the multiple date range, I can't seem to figure it out. Any help would be appreciated. Thank you!

[shilpan]

Use Extended intervalmatch prefix

[Thiago_Justen_]

Try this:

Inner Join

IntervalMatch (Date,Key)

LOAD

      DateStart,

      DateEnd,

      Key

Resident TableA;

[melissapluke]

This works but ends up creating a synthetic key. If I drop the fields from the original table, it no longer works for me.

[Thiago_Justen_]

Melissa,

Could you please share this piece of script?

[melissapluke]

Sure, thanks!

Here you go:

[Data]:

Load

   Key,

   "ID",

    "Date",

From xyz;

Contact:

Load

     Key,

     Contact,

     "Effective Date",

     "Effective Date End",

     Rate

From abc;

Inner Join ([Data])

IntervalMatch ("Date",Key)

LOAD

     "Effective Date",

    "Effective Date End",

     Key

Resident Contact;

[Thiago_Justen_]

Try this:

[Data]:

Load

   Key,

   "ID",

    "Date",

From xyz;

Contact:

Load

     Key,

     Contact,

     "Effective Date",

     "Effective Date End",

     Rate

From abc;

Inner Join //omit the table

IntervalMatch ("Date",Key)

LOAD

     "Effective Date",

    "Effective Date End",

     Key

Resident Contact;

[melissapluke]

Hi,

Thanks, the issue with this solution is that is sometimes I need to view ALL the contact records, whether they are in the Data table or not. Doing this inner join seems to limit the Contacts table to only what is in the Data table. 

[Thiago_Justen_ · Accepted solution]

Melissa, let's try something new:

Table_A_Temp:

LOAD * Inline [

Key,     DateStart,          DateEnd,         Rate

ABCD,    1/1/2016,            31/12/2016,        10

ABCD,    1/1/2017,            31/12/2017,        8

WXYZ,    1/1/2016,            31/12/2016,        11

WXYZ,    1/1/2017,            31/12/2017,        12

];

Table_B:

LOAD

*,

    Key&Date AS CompoundKey;

LOAD * Inline [

ID,        Key,    Date

1,          ABCD,     15/07/2016

2,          ABCD,     30/12/2017

3,          ABCD,     1/1/2018

4,          WXYZ,     2/2/2016

];

Bridge:

IntervalMatch(Date,Key)

Load

DateStart,

    DateEnd,

    Key

Resident Table_A_Temp;

Left Join (Table_A_Temp)

Load

*

Resident Bridge;

Drop Table Bridge;

Table_A:

Load

*,

    Key&Date AS CompoundKey

Resident Table_A_Temp;

Drop Table Table_A_Temp;

Drop Fields Date,Key From Table_A;

The result, as you need, is below:

[Thiago_Justen_]

If you want to check my example app, here it is. !