Add sales ranges to sales order table

[SBN]

Hi there fellow Qlik enthousiasts,

I have the following two tables in my simplified example.

[Orders]:
Load * Inline [
Order,Date,CustomerID,SalespersonID,Value
1,1-5-2023,1,1,100
2,1-5-2023,2,2,350
3,2-5-2023,1,2,1500
4,2-5-2023,2,2,10020
5,3-5-2023,1,1,221000
6,4-5-2023,2,1,27500
7,2-5-2023,2,3,750000
8,4-5-2023,1,3,85000
];

[OrderSize]:
Load * Inline [
OrderSizeCode, OrderSizeFrom, OrderSizeTo, OrderSizeDescription
01,0,250,01. € 0 up and to € 250
02,251,500,02. € 251 up and to € 500
03,501,1000,03. € 501 up and to € 1.000
04,1001,2000,04. € 1.001 up and to € 2.000
05,2001,5000,05. € 2.001 up and to € 5.000
06,5001,10000,06. € 5.001 up and to € 10.000
07,10001,25000,07. € 10.001 up and to € 25.000
08,25001,50000,08. € 25.001 up and to € 50.000
09,50001,75000,09. € 50.001 up and to € 75.000
10,75001,150000,10. € 75.001 up and to € 150.000
11,150001,250000,11. € 150.001 up and to € 250.000
12,250001,500000,12. € 250.001 up and to € 500.000
13,500001,100000000,13. from € 500.001
];

I have figured out that I can use the "IntervalMatch" function to create a "bridge" table where I can match the [Value]  from the [Orders] table with the [OrderSizeFrom] and [OrderSizeTo] from the [OrderSize] table.

[BridgeTableOrderSize]:
IntervalMatch ([Value])
LOAD distinct [OrderSizeFrom], [OrderSizeTo]
Resident [OrderSize];

If I'm not mistaken this use the field [Value]  as the key.

In my more complex data model I also have a table with [Opportunities] and [Quotations] where I'd like to apply the [OrderSize] table as well.
The [OrderSize] table would be loaded from an Excel file so the customer will be able to set there own sizes.
Preferably without using additional IntervalMatch tables

If it's possible I would like to end up with the following [Orders] table with an additional column with the "OrderSizeCode" from the [OrderSize] table.

[Orders]:
Load * Inline [
Order,Date,CustomerID,SalespersonID,Value,OrderSizeCode
1,1-5-2023,1,1,100,01
2,1-5-2023,2,2,350,02
3,2-5-2023,1,2,1500,04
4,2-5-2023,2,2,10020,07
5,3-5-2023,1,1,221000,11
6,4-5-2023,2,1,27500,08
7,2-5-2023,2,3,750000,13
8,4-5-2023,1,3,85000,10
];

Is this possible?

I attached an example QVF to play with...😉
Thanks in advance for your suggestions.

Kind regards,

SBN

[SBN · Accepted solution]

Hi there,

I might have found the solution myself.

• I changed the [Orders]  table to [OrdersTemp];
• I added the following mapping load table:
  
  

  [OrderSizeFromMapping]:
  Mapping Load Distinct [Value],
  [OrderSizeFrom]
  Resident [BridgeTableOrderSize];
  
  With this table I'm mapping the [OrderSizeFrom] value to the [Value] from the [Orders] by loading it from the [BridgeTableOrderSize];

• I added another mapping load table:
  
  [OrderSizeCodeMapping]:
  Mapping Load [OrderSizeFrom],
  [OrderSizeCode]
  Resident [OrderSize];
  
  With this table I'm mapping the [OrderSizeFrom] to the [OrderSizeCode] from the [OrderSize] table;
  
  
• I load the [Orders] table from the [OrdersTemp] table with the following load script:
  
  [Orders]:
  Load *,
  ApplyMap('OrderSizeCodeMapping', ApplyMap('OrderSizeFromMapping', [Value]), Null()) as [OrderSizeCode]
  Resident [OrdersTemp];
  
  
• And finally I'm dropping the [BridgeTableOrderSize] and the [OrdersTemp] table:
  
  Drop Tables [OrdersTemp], [BridgeTableOrderSize];

This combined results in the following data model:

I attached the final simplified version for future reference.

Is this the way to solve this or is there a better, more efficient way?

Kind regards,

SBN