between dates scripting

[MarivdBroek]

Hello,

I want to combine 2 reports.

Report 1 (no problem):

Receipts:

DateReceipt   -Price    Curr

10-2-2022        7           USD

14-4-2022        10         USD

Report 2:

Rates:

Row Curr      from      rate

1       USD      1-1-2022   1,12

2       USD       5-3-2022   1,13

3       USD      15-4-2022   1,14

So Between 1-1-22 and 4-3-22 the rate was 1,12, between 5-3-22 and 15-4-22 it was 1,13 and from 15-4-22 up to now 1,14.

combination: if(Date Receipt >= date1 and < date 2 (next) , 1,12,  Result:

DateReceipt    Curr   Price    Rate

10-2-22             USD    7          1,12

14-4-22             USD   10        1,13

[Dalton_Ruer · Accepted solution]

I think you will need 2 steps. 

Step 1: Walk through the Interest Rate table using the Previous or Peek functions so you can establish the From - To values by sorting in descending order. 

Step 2: Use the Interval function to then match your transactions to the "from/to intervals" for your interest rates. 

[MarcoWedel]

maybe helpful:

https://community.qlik.com/t5/Design/Creating-a-Date-Interval-from-a-Single-Date/ba-p/1467840

https://community.qlik.com/t5/QlikView-Documents/IntervalMatch-and-Slowly-Changing-Dimensions/ta-p/1...

https://community.qlik.com/t5/Design/Slowly-Changing-Dimensions/ba-p/1464187

[Dalton_Ruer]

Handy examples.