Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ksharpes
Creator
Creator

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

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

11 Replies
sunny_talwar

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

ksharpes
Creator
Creator

The below table is essentially what i want to see.

Product NameSeller with Lowest PriceLowest Price
Product 1Seller A1.1
Product 2Seller C1.0

 

Sample data

Product 11.1Seller A1100
Product 11.15Seller A1150
Product 11.2Seller A1200
Product 11.2Seller B1200
Product 11.25Seller B1250
Product 11.3Seller B1300
Product 11.3Seller C1300
Product 11.35Seller C1350
Product 11.4Seller C1400
Product 21.1Seller A1100
Product 21.15Seller A1150
Product 21.2Seller B1200
Product 21.25Seller B1250
Product 21.0Seller C1000
Product 21.35Seller C1350
sunny_talwar

Where is Exclude and International fields here?

ksharpes
Creator
Creator

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

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

That will just give me the below, I will also need the last column "Seller with Lowest Price"

 

ProductPriceSellerVolumeMin PriceSeller with Lowest Price
Product 11.1Seller A11001.1Seller A
Product 11.15Seller A11501.1Seller A
Product 11.2Seller A12001.1Seller A
Product 11.2Seller B12001.1Seller A
Product 11.25Seller B12501.1Seller A
Product 11.3Seller B13001.1Seller A
Product 11.3Seller C13001.1Seller A
Product 11.35Seller C13501.1Seller A
Product 11.4Seller C14001.1Seller A
Product 21.1Seller A11001.0Seller C
Product 21.15Seller A11501.0Seller C
Product 21.2Seller B12001.0Seller C
Product 21.25Seller B12501.0Seller C
Product 21.0Seller C10001.0Seller C
Product 21.35Seller C13501.0Seller 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