Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

avrahamb
New Contributor II

Straight Table - how to get the cheapest vendor reference

Hello,

i've got this kind of data set:

    

QCD_Material_Cost (Per Unit)QCD_QC_NbrQCD_Component_PNLowest Quotes PriceQuotation nbr
524452590
5244525900100-2510524452590
9744591100100-251052
25644588000100-251052
36644566510100-251052
38644587904985-4444524458790
56144538704985-444452

i've got the lowest price and the quote number with the following expressions:

Lowest Quotes Price: min(TOTAL <QCD_Component_PN> [QCD_Material_Cost (Per Unit)])

Quotation nbr: concat( aggr(if(min([QCD_Material_Cost (Per Unit)])=min(total <QCD_Component_PN> [QCD_Material_Cost (Per Unit)]),QCD_QC_Nbr),QCD_QC_Nbr,QCD_Component_PN),', ')

i woudl like that the  Quotation nbr of the lowest price will be listed for the other lines too.

so i will get this table

CD_Material_Cost (Per Unit)QCD_QC_NbrQCD_Component_PNLowest Quotes PriceQuotation nbr
524452590
5244525900100-2510524452590
9744591100100-2510524452590
25644588000100-2510524452590
36644566510100-2510524452590
38644587904985-4444524458790
56144538704985-444452

4458790

Thanks, Barak

17 Replies
avrahamb
New Contributor II

Re: Straight Table - how to get the cheapest vendor reference

concat(total <QCD_Component_PN> aggr(if(min([QCD_Material_Cost (Per Unit)])=min(total <QCD_Component_PN> [QCD_Material_Cost (Per Unit)]),QCD_QC_Nbr),QCD_QC_Nbr,QCD_Component_PN),', ')

add the total qualifier on the concat

Highlighted
amirkachlon
New Contributor III

Re: Straight Table - how to get the cheapest vendor reference

concat(total <QCD_Component_PN> aggr(if(min([QCD_Material_Cost (Per Unit)])=min(total <QCD_Component_PN> [QCD_Material_Cost (Per Unit)]),QCD_QC_Nbr),QCD_QC_Nbr,QCD_Component_PN),', ')

vinieme12
Esteemed Contributor II

Re: Straight Table - how to get the cheapest vendor reference

Now I see what you need, just use the below


=FirstSortedValue(TOTAL <QCD_Component_PN> QCD_QC_Nbr,QCD_Material_Cost)

girirajsinh
Contributor III

Re: Straight Table - how to get the cheapest vendor reference

Punit's answer would be much simpler and straight forward to use instead of concate(...) one

avrahamb
New Contributor II

Re: Straight Table - how to get the cheapest vendor reference

Hello Purjari, in case 2 vendors has minmum price it will return only 1 of them

thanks, Barak

vinieme12
Esteemed Contributor II

Re: Straight Table - how to get the cheapest vendor reference

You can tweak the expression like below

= FirstSortedValue(TOTAL<QCD_Component_PN> AGGR(concat(DISTINCT QCD_QC_Nbr,','),QCD_Material_Cost) , aggr(QCD_Material_Cost,QCD_Material_Cost))

avrahamb
New Contributor II

Re: Straight Table - how to get the cheapest vendor reference

Hello Pujari,

your expression responds to user selections, can you please advise how to implement for global implemanation ?

the number on the second table should be 44555660

Thanks!

= FirstSortedValue(total <QCD_Component_PN>AGGR(concat( QCD_QC_Nbr,','),[QCD_Material_Cost (Per Unit)]) , aggr([QCD_Material_Cost (Per Unit)],[QCD_Material_Cost (Per Unit)]))

Capture.PNGCapture.PNG

avrahamb
New Contributor II

Re: Straight Table - how to get the cheapest vendor reference

Hello Amir,

your exspression has the same issue, its sensetive to user selections and does not provides with global answer.

(see my respond to Punit)

Thanks

vinieme12
Esteemed Contributor II

Re: Straight Table - how to get the cheapest vendor reference

Use below

= FirstSortedValue({1}TOTAL<QCD_Component_PN> AGGR(concat({1}DISTINCT QCD_QC_Nbr,','),QCD_Material_Cost) , aggr(QCD_Material_Cost,QCD_Material_Cost))