topic Re: Date between range - how to connect tables in App Development

Date between range - how to connect tables

midnight1 — Wed, 12 Feb 2025 13:52:57 GMT

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.

Re: Date between range - how to connect tables

Qrishna — Wed, 12 Feb 2025 14:23:52 GMT

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

Re: Date between range - how to connect tables

marcus_sommer — Wed, 12 Feb 2025 14:55:04 GMT

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;

Re: Date between range - how to connect tables

midnight1 — Wed, 12 Feb 2025 15:17:15 GMT

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

Re: Date between range - how to connect tables

marcus_sommer — Wed, 12 Feb 2025 15:26:20 GMT

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.

Re: Date between range - how to connect tables

Vegar — Wed, 12 Feb 2025 23:01:19 GMT

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;

Re: Date between range - how to connect tables

MarcoWedel — Mon, 17 Feb 2025 16:44:41 GMT

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/ScriptPrefixes/IntervalMatch.htm#:~:text=When%20the%20IntervalMatch%20prefix%20is%20extended%20with%20key%20fields%2C%20it%20is%20used%20to%20create%20a%20table%20matching%20discrete%20numeric%20values%20to%20one%20or%20more%20numeric%20intervals%2C%20while%20at%20the%20same%20time%20matching%20the%20values%20of%20one%20or%20several%20additional%20keys.