Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
avrahamb
Contributor II
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
Contributor II
Contributor II
Author

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

amirkachlon
Contributor III
Contributor III

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

Now I see what you need, just use the below


=FirstSortedValue(TOTAL <QCD_Component_PN> QCD_QC_Nbr,QCD_Material_Cost)

girirajsinh
Creator III
Creator III

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

avrahamb
Contributor II
Contributor II
Author

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

thanks, Barak

vinieme12
Champion II
Champion II

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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

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