Date between range - how to connect tables

[midnight1]

Hi 🙂

I have two tables:

First table:

Orders:
Load 
    OrderId
    Type_of_offer,  // additional value --> ArtId and Type_of_offer is like a Key 
    ArtId,
    Date_of_order
From Orders;

Second Table:

Prices:
LOAD
    ArtId,
    Type_of_offer,
    Date_Price_from,
    Date_Price_to,
    Price
FROM Prices;

I want to have a price of article in orders at the time the order was created with particular type of order - so the order_date has to be between date_from and date_to for key pair ArtId and Type_of_offer

I use 

ArtId&'/'&Type_of_offer as Key

since the price will be different for articles depending on date and type_of_offer.

How to connect this? I heard of Intervalmatch() but i don't know how to use it properly. Table with orders is quite big and i don't know which solution is the best.

[Qrishna]

Pls provide some sample data and expected output. data speaks more than what we would like to explain. thnx

[marcus_sommer]

I would directly add the price to the orders - in this way:

m: mapping load
   date(Date_Price_from + iterno() -1) & '|' & ArtId & '|' & Type_of_offer, Price
from Prices while Date_Price_from + iterno() -1 <= Date_Price_to;

Orders: load *, applymap('m', Date_of_order & '|' & ArtId & '|' & Type_of_offer, '#NV') as Price
from Orders;

[midnight1]

You're right, I should have added something:

 Although this piece of data might have similar data, it shows that if the order for some article is placed with some type_of_offer i want to find the price for this pair ArtId&type_of_offer judging by date_of order which has to be between two dates from price table for that ArtId&type_of_offer pair

[marcus_sommer]

The mapping will do the job because an internal load-loop per while + iterno() is included which resolved the fom-to area to a dedicated date which is then string-concatenated with the other parameter. Just comment the mapping-statement and take then a look on the table in the data-model or the UI.

[Vegar · Accepted solution]

You could also go for an intervalmatch () approach to solve this.

First load your two tables as described in the post.

Then add the following script

//left join (Orders) //is an option if you don't want the intervalmatch table in between the two original tables.

Intervalmatch_table:

Intervalmatch (Date_of_order,ArtId,Type_of_offer)

Load

Date_Price_from,

Date_Price_to,

ArtId,

Type_of_offer

Resident Prices;

[MarcoWedel]

maybe helpful:

IntervalMatch and Slowly Changing Dimensions - Qlik Community - 1479928

Slowly Changing Dimensions - Qlik Community - 1464187


https://help.qlik.com/en-US/sense/November2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref....