Showing the lowest value and which customer it relates to in Script

[ksharpes]

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];

[sunny_talwar]

Would you be able to share some sample data and the output you expect to see from it?

[ksharpes]

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

[sunny_talwar]

Where is Exclude and International fields here?

[ksharpes]

Those fields can be ignored for now

[sunny_talwar]

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;

[ksharpes]

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

[sunny_talwar]

Why won't it give you the customer?

[ksharpes]

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

[sunny_talwar]

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