Interval Match

[racer25]

Hi

I believe the answer is IntervalMatch and possibly left join? But I have not been able to get working hence reaching out for help.

My code is below; The Inline table is not the full table the CURR is real.

Bassically I have a billing table with multi currency information and then an FX Rates table which stores all rates with To and From.

I need to get the FX Rate joined into the Billing Table so I can perform calculations.

I'd appreciate any help.

Thanks,

Rob

BILLING:
LOAD * INLINE [
    Currency, DATE, Invoice, Amount
    EUR, 01/10/2016, 1, 100
    EUR, 15/11/2016, 2, 200,
    GBP, 08/12/2016, 3, 250
];




OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle;Persist Security Info=True;User ID=;Data Source=scsdowp0;Extended Properties=""] (XPassword is C);


TEMPCURR:
LOAD "CURR_CODE",
    "CURR_NAME",
    "BASE_CURR_CODE",
    "CURR_DATE",
    "CURR_EOM_RATE_FC_BASE"
    where BASE_CURR_CODE ='USD'
    ;
SQL SELECT "CURR_CODE",
    "CURR_NAME",
    "BASE_CURR_CODE",
    "CURR_DATE",
     "CURR_EOM_RATE_FC_BASE"
FROM SDO."AON_CURR_MONTHLY_EXCH_RATE_SDO"
;


NoConcatenate
CURR:
LOAD
  CURR_CODE as Currency,
  CURR_NAME as CurrencyName,
  BASE_CURR_CODE as Base_Currency,
  CURR_EOM_RATE_FC_BASE as Rate,
  CURR_DATE as CurrTo,
  previous(CURR_DATE) as CurrFrom
Resident TEMPCURR
Order by CURR_CODE, CURR_DATE;


Drop Table TEMPCURR;

[micheledenardi]

  Maybe something like this:

BILLING:  
Load *,
DATE&'|'&Currency as [%Key Currency Excahnge]
;

LOAD * INLINE [  
    Currency, DATE, Invoice, Amount  
    EUR, 01/10/2016, 1, 100  
    EUR, 15/11/2016, 2, 200,   
    GBP, 08/12/2016, 3, 250  
];  


OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle;Persist Security Info=True;User ID=;Data Source=scsdowp0;Extended Properties=""] (XPassword is C);  
  
Left join(BILLING)

//CURR:  
LOAD 
    "CURR_DATE"&'|'&"CURR_CODE" as [%Key Currency Excahnge],

    "CURR_CODE" as Currency,  
    "CURR_NAME"  as CurrencyName,  
    "BASE_CURR_CODE" as Base_Currency,  
    "CURR_DATE" as CurrTo,  
    "CURR_EOM_RATE_FC_BASE" as Rate,
    previous(CURR_DATE) as CurrFrom  
    where BASE_CURR_CODE ='USD'  
    ;  
SQL SELECT "CURR_CODE",  
    "CURR_NAME",  
    "BASE_CURR_CODE",  
    "CURR_DATE",  
     "CURR_EOM_RATE_FC_BASE"  
FROM SDO."AON_CURR_MONTHLY_EXCH_RATE_SDO" 

[racer25]

Hi Michele,

Thanks for taking a look;

Your proposal will not work as I have a to and from in my FX Rates - the rate is X from 1/1/17 to 31/1/17 but my billing could be any date in between - that is where I was thing of IntervalMatch,

Thanks,

Rob

[annafuksa1]

Can you post the app with example of data ?

[racer25]

Sample file and Sample Data now attached to original post.

[annafuksa1]

Sorry, I made my own data

[annafuksa1 · Accepted solution]

and with your data

[kusumanchir]

Hi Robert,

Please find the attached application and let me know if this is output you are ecpecting.

Regards,

Rasly.K

[kusumanchir]

Hi Robert,

Please find the attached app with all the information required in the Billing table.

[racer25]

Thanks Anna

Much Appreciated.