topic Re: Interval Match in QlikView

Interval Match

racer25 — Thu, 03 Aug 2017 08:33:49 GMT

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;

Re: Interval Match

micheledenardi — Thu, 03 Aug 2017 08:57:11 GMT

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"

Re: Interval Match

racer25 — Thu, 03 Aug 2017 09:06:39 GMT

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

Re: Interval Match

annafuksa1 — Thu, 03 Aug 2017 09:20:08 GMT

Can you post the app with example of data ?

Re: Interval Match

racer25 — Thu, 03 Aug 2017 10:35:56 GMT

Sample file and Sample Data now attached to original post.

Re: Interval Match

annafuksa1 — Thu, 03 Aug 2017 10:41:15 GMT

Sorry, I made my own data

Re: Interval Match

annafuksa1 — Thu, 03 Aug 2017 10:56:54 GMT

and with your data

Re: Interval Match

kusumanchir — Thu, 03 Aug 2017 11:03:30 GMT

Hi Robert,

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

Regards,

Rasly.K

Re: Interval Match

kusumanchir — Thu, 03 Aug 2017 11:13:18 GMT

Hi Robert,

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

Re: Interval Match

racer25 — Thu, 03 Aug 2017 11:15:46 GMT

Thanks Anna

Much Appreciated.

Re: Interval Match

racer25 — Thu, 03 Aug 2017 11:21:39 GMT

Thanks Rasly - Different to Anna's but equally corrrect.

I will test both with 1000's of invoices and see which is most efficient.

Appreciate your time

Re: Interval Match

Kushal_Chawda — Thu, 03 Aug 2017 11:25:57 GMT

In below example, you have two records for,CurrFrom &CurrTo, so on 26/04/2016 which Rate should be consider?

Base_Currency	Currency	CurrencyName	Rate	CurrFrom	CurrTo	AllDates
USD	TZS	TANZANIAN SHILLING	0.000454	26/03/2016	26/04/2016	26/04/2016
USD	TZS	TANZANIAN SHILLING	0.000456	26/04/2016	26/05/2016	26/04/2016

Re: Interval Match

kusumanchir — Thu, 03 Aug 2017 11:35:41 GMT

Hi Robert,

Thank you.

In my code, I created a key using 'Currency and Date' in order to link to Billing table. I forgot to use Autonumber() which might increase performance. Could you please add and try it.

Regards,

Rasly.K

Re: Interval Match

racer25 — Thu, 03 Aug 2017 12:10:54 GMT

Thanks Kushal - I was using Previous to get the Previous FX Date but based on your feedback I have taken CurrFrom + 1