Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
racer25
Contributor III
Contributor III

Interval Match

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;

1 Solution

Accepted Solutions
annafuksa1
Creator III
Creator III

and with your data

View solution in original post

13 Replies
micheledenardi
Specialist II
Specialist II

  Maybe something like this:


  1. BILLING: 
  2. Load *,
  3. DATE&'|'&Currency as [%Key Currency Excahnge]
  4. ;
  5. LOAD * INLINE [ 
  6.     Currency, DATE, Invoice, Amount 
  7.     EUR, 01/10/2016, 1, 100 
  8.     EUR, 15/11/2016, 2, 200,  
  9.     GBP, 08/12/2016, 3, 250 
  10. ]; 

  11. OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle;Persist Security Info=True;User ID=;Data Source=scsdowp0;Extended Properties=""] (XPassword is C); 
  12.  
  13. Left join(BILLING)
  14. //CURR: 
  15. LOAD
  16.     "CURR_DATE"&'|'&"CURR_CODE" as [%Key Currency Excahnge],
  17.     "CURR_CODE" as Currency
  18.     "CURR_NAME"  as CurrencyName
  19.     "BASE_CURR_CODE" as Base_Currency
  20.     "CURR_DATE" as CurrTo
  21.     "CURR_EOM_RATE_FC_BASE" as Rate,
  22.     previous(CURR_DATE) as CurrFrom 
  23.     where BASE_CURR_CODE ='USD' 
  24.     ; 
  25. SQL SELECT "CURR_CODE", 
  26.     "CURR_NAME", 
  27.     "BASE_CURR_CODE", 
  28.     "CURR_DATE", 
  29.      "CURR_EOM_RATE_FC_BASE" 
  30. FROM SDO."AON_CURR_MONTHLY_EXCH_RATE_SDO"
Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
racer25
Contributor III
Contributor III
Author

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
Creator III
Creator III

Can you post the app with example of data ?

racer25
Contributor III
Contributor III
Author

Sample file and Sample Data now attached to original post.

annafuksa1
Creator III
Creator III

Sorry, I made my own data

annafuksa1
Creator III
Creator III

and with your data

kusumanchir
Creator
Creator

Hi Robert,

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

Regards,

Rasly.K

kusumanchir
Creator
Creator

Hi Robert,

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

racer25
Contributor III
Contributor III
Author

Thanks Anna

Much Appreciated.