Load script: Add columns from one table to another with no common and condition

[jamesehunt]

Hi all,

I would like to add columns from one table to another. I have one table calculating the inbound volume (Table 1)and I would like the add the pricing for each column from another table (Tabel 2) checking if the Date from Table 1 is ">=" Date from Table 2 in order to pick the correct pricing for the day the service has been provided.

Ideally I would like Table 1 to show the pricing per column (in an extra column) and multiply the pricing per column with the value in the field. 

The pricing will always have a date from when the agreed pricing kicks in. The new pricing will be added in a new row with the starting date (see table 2). It could be that the pricing doesn't change for years.

Table 1: 

Load script:

//---Inbound
Inbound1:
Load
Date,
TPIDEN,
TPBST1,
TPBMEN,
TPLENR,
TPVPE

Resident PHISTTP
where Date > '01.04.2020' and TPVORT = 'WRA'and TPVBER = 'PJ' and TPNSST = '0' and match (TPTTYP,'U','UK');

Inbound:
Load
Date,
Count(Distinct TPBST1) as "Inbound Preadvice", // Pre-Advice
Count(Distinct TPIDEN&TPBST1) as "Inbound Items", // Inbound Items
sum(TPBMEN) as "Inbound Pieces", // Inbound Pieces
ceil(Sum(TPBMEN/TPVPE)) as "Inbound Sorted Cartons", // Inbound Cartons
Count(Distinct TPLENR) as "Inbound Put Away Pallet" // Put Away Pallets
Resident Inbound1
Group by Date;

Drop Table Inbound1;

Table 2:

Load script:

Pricing:
LOAD
"Date Price Agreement",
"Container Unloading",
Sorting,
"Relocation Wrapping and Put away"
FROM [lib://Pricing/Qlik Sense Pricing.xlsx]
(ooxml, embedded labels, table is [Pricing Matrix]);

Looking forward to your thoughts

[JustinDallas]

If they don't have a common condition, then how do you match them up ?  Do you have any dummy data we can work with?  It sounds like you are doing a style of interval matching, but I can't really tell with the blend of data model work and UI work that you've got going on.

[jamesehunt]

@JustinDallas I need to multiply the value in column "Inbound Preadvice" from Table1 with the value in column "Container Unloading" from Table2 when the value of the "Date"-column in Table1 is bigger than the value of "Date Price Agreement" in Table 2. So for each row in Table1 I need to first find the right row in Table2 and then multiply the right values to get the right price. So I actually only the result of this calculation to be displayed in my new table (or extend my existing Table1).

For example:

In Table1 I have the row

Inbound.Date	Inbound Preadvice	Inbound Items	Inbound Pieces	Inbound Sorted Cartons	Inbound Put Away Pallet
10.04.2020	2	9	10963	1495	42
02.06.2020	4	16	12456	2193	50

and in Table2 I have

Date Price Agreement	Container Unloading	Sorting	Relocation Wrapping and Put away
01.04.2020	2€	5€	100€
01.06.2020	4€	10€	200€

then ideally I would like a result of:

Inbound.Date	Inbound Preadvice	Inbound Items	Inbound Pieces	Inbound Sorted Cartons	Inbound Put Away Pallet	Preadvice Price
10.04.2020	2	9	10963	1495	42	4€
02.06.2020	4	16	12456	2193	50	16€