Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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:
Use Extended intervalmatch prefix
Try this:
Inner Join
IntervalMatch (Date,Key)
LOAD
DateStart,
DateEnd,
Key
Resident TableA;
This works but ends up creating a synthetic key. If I drop the fields from the original table, it no longer works for me.
Melissa,
Could you please share this piece of script?
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;
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;
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.
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:
If you want to check my example app, here it is. !