How can we use similar function as Vlookup??

sabaidee555
sabaidee555 Member
edited March 2021 in Magic ETL

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???

Answers

  • 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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"