4 Replies Latest reply on Oct 6, 2018 9:28 AM by Avi Meir

# Finding the cheapest supplier, and average distance from it

Hi all, I have the following data:

 id amount currency provider flight_designators class transaction_id 1 996.45 EUR prov1 {LX242,LX243} economy 123 2 1010.49 EUR prov3 {LX242,LX243} economy 123 3 1005.23 EUR prov4 {LX242,LX243} economy 123 4 2938 EUR prov1 {LX242,LX243} economy-comfort 123 5 2931 EUR prov2 {LX242,LX243} economy-comfort 123 6 3002 EUR prov4 {LX242,LX243} economy-comfort 123 7 720 EUR prov1 {LH2305,AF1820} economy 123 8 910 EUR prov3 {LH2305,AF1820} economy 123 9 801 EUR prov4 {LH2305,AF1820} economy 123 10 1028 EUR prov1 {LH2305,AF1820} economy-comfort 123 11 1038 EUR prov2 {LH2305,AF1820} economy-comfort 123 12 1000 EUR prov4 {LH2305,AF1820} economy-comfort 123

I am trying to do the following:

For each transaction ID, class, and flight_designators combination:

1. show the cheapest provider
2. for each provider, show the distance, in %, from the cheapest provider

So in the example above, it should look as follows:

 id amount currency provider flight_designators class transaction_id cheapest distance from cheapest 1 996.45 EUR prov1 {LX242,LX243} economy 123 prov1 0% 2 1010.49 EUR prov3 {LX242,LX243} economy 123 prov1 1.41% 3 1005.23 EUR prov4 {LX242,LX243} economy 123 prov1 0.88% 4 2938 EUR prov1 {LX242,LX243} economy-comfort 123 prov2 0.24% 5 2931 EUR prov2 {LX242,LX243} economy-comfort 123 prov2 0.00% 6 3002 EUR prov4 {LX242,LX243} economy-comfort 123 prov2 2.42% 7 720 EUR prov1 {LH2305,AF1820} economy 123 prov1 0% 8 910 EUR prov3 {LH2305,AF1820} economy 123 prov1 26.39% 9 801 EUR prov4 {LH2305,AF1820} economy 123 prov1 11.25% 10 1028 EUR prov1 {LH2305,AF1820} economy-comfort 123 prov4 2.80% 11 1038 EUR prov2 {LH2305,AF1820} economy-comfort 123 prov4 3.80% 12 1000 EUR prov4 {LH2305,AF1820} economy-comfort 123 prov4 0.00%
• ###### 1. Re: Finding the cheapest supplier, and average distance from it

Hello Avi,

You can try below approach :

Create below logic:

1. cheapest provider amount = { FIXED [Transaction Id],[Class],[Flight Designators]:min([Amount])}

2. cheapest = { FIXED [Class],[Flight Designators],[Transaction Id]: MAX(if [cheapest provider amount]=[Amount] then [Provider] END)}

3. % diff = ([Amount]-[cheapest provider amount])/[cheapest provider amount]

change the number format of  [% diff]  to Percentage.

• ###### 2. Re: Finding the cheapest supplier, and average distance from it

Thanks!

I tried you suggestion, but there seems to be some calculation running forever. "Executing query" has been running for 15 minutes now...

I added a filter to see if it's just too much data, but the same happens.

Any idea?

• ###### 3. Re: Finding the cheapest supplier, and average distance from it

Hi Avi,

What's the data volume are you using??

For performance issue , best thing i can suggest is to go for "Performance Recording"

• ###### 4. Re: Finding the cheapest supplier, and average distance from it

Nothing too crazy I think, 34m rows in a postgresql database with live connection on a  200mbs symmetrical fiber optic connection