How can we use similar function as Vlookup??

sabaidee555
sabaidee555 ⚪️
edited March 18 in Dataflows

I would like to offer a volume discount price for each company A, B, C and calculate total biil.

There are 2 datasets like the below. When we sell 250 of Products to Company A in 2021 Feb (Dataset B), I would like to apply Price 4 USD on the price list for Company A (Dataset A). Then the total bill will be 1000 USD.

If it's just 200 Selling to company A, 4 USD will be applied.



Dataset A

Price List in USD

Number of Selling  Company A Company B Company C

1            6      7      5

100           5      6      5

200           4      5      4

300           2      4      4


Dataset B

Number of Selling

Month   Company A  Company B  Company C

2021 Jan   200     150      160

2021 Feb   250     250      350

2021 Mar   100     180      230

2021 Apr   230     200      50



Output Dataset

Total Billing in USD

Month  Company A  Company B  Company C

2021 Jan   800     900      800

2021 Feb   1000    1250     1400

2021 Mar   500     1080     920

2021 Apr   920     1000     200


Can anyone help me???

Tagged:

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    you have to alter dataset_a to include a column, min_units and max_units

    then you JOIN dataset_a to dataset_b on a BETWEEN clause in MySQL