Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
From the previous thread
Re: Returning seller name where selling price is the lowest.
I believe i now need this done within the script as i want to count the number of results where a customer has the lowest price.
The following however, gives me a minimum Price for each customer for [Common Name] but i only want the lowest Price for the [Common Name] and whichever [Customer] that would be. Am i on the right track here or does it need to be done a different way?
CustomerMIN:
Load
[Customer],
[Common Name],
Min([Unit Price]) as MINPRICE
Resident Data
where "Exclude" = 'No' and [International] = 'Yes'
Group by [Customer],[Common Name];
Would you be able to share some sample data and the output you expect to see from it?
The below table is essentially what i want to see.
Product Name | Seller with Lowest Price | Lowest Price |
Product 1 | Seller A | 1.1 |
Product 2 | Seller C | 1.0 |
Sample data
Product 1 | 1.1 | Seller A | 1100 |
Product 1 | 1.15 | Seller A | 1150 |
Product 1 | 1.2 | Seller A | 1200 |
Product 1 | 1.2 | Seller B | 1200 |
Product 1 | 1.25 | Seller B | 1250 |
Product 1 | 1.3 | Seller B | 1300 |
Product 1 | 1.3 | Seller C | 1300 |
Product 1 | 1.35 | Seller C | 1350 |
Product 1 | 1.4 | Seller C | 1400 |
Product 2 | 1.1 | Seller A | 1100 |
Product 2 | 1.15 | Seller A | 1150 |
Product 2 | 1.2 | Seller B | 1200 |
Product 2 | 1.25 | Seller B | 1250 |
Product 2 | 1.0 | Seller C | 1000 |
Product 2 | 1.35 | Seller C | 1350 |
Where is Exclude and International fields here?
Those fields can be ignored for now
Try something like this
Table:
LOAD Product,
Price,
Seller,
Volume
FROM ...;
Right Join (Table)
LOAD Product,
Min(Price) as Price
Resident Table
Group By Product;
That will give me the minimum price for the Product but it wont give me the customer that sold it.
I need to find the seller with the lowest price then join that to the Product. For some reason I cant work this out ><
Why won't it give you the customer?
That will just give me the below, I will also need the last column "Seller with Lowest Price"
Product | Price | Seller | Volume | Min Price | Seller with Lowest Price |
Product 1 | 1.1 | Seller A | 1100 | 1.1 | Seller A |
Product 1 | 1.15 | Seller A | 1150 | 1.1 | Seller A |
Product 1 | 1.2 | Seller A | 1200 | 1.1 | Seller A |
Product 1 | 1.2 | Seller B | 1200 | 1.1 | Seller A |
Product 1 | 1.25 | Seller B | 1250 | 1.1 | Seller A |
Product 1 | 1.3 | Seller B | 1300 | 1.1 | Seller A |
Product 1 | 1.3 | Seller C | 1300 | 1.1 | Seller A |
Product 1 | 1.35 | Seller C | 1350 | 1.1 | Seller A |
Product 1 | 1.4 | Seller C | 1400 | 1.1 | Seller A |
Product 2 | 1.1 | Seller A | 1100 | 1.0 | Seller C |
Product 2 | 1.15 | Seller A | 1150 | 1.0 | Seller C |
Product 2 | 1.2 | Seller B | 1200 | 1.0 | Seller C |
Product 2 | 1.25 | Seller B | 1250 | 1.0 | Seller C |
Product 2 | 1.0 | Seller C | 1000 | 1.0 | Seller C |
Product 2 | 1.35 | Seller C | 1350 | 1.0 | Seller C |
This was not what you wanted when we started :)... try this
Table:
LOAD Product,
Price,
Seller,
Volume
FROM ...;
Left Join (Table)
LOAD Product,
Min(Price) as [Min Price],
FirstSortedValue(Seller, Price) as [Seller with Lowest Price]
Resident Table
Group By Product;
It is easier to help when you provide the output you are looking to get. It help us avoid unnecessary rework.
Best,
Sunny