Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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

sorry, this wont work, it shows all of the other refrences

vinieme12
Champion II
Champion II

Can you post a snapshot?

vinieme12
Champion II
Champion II

or just add the fields you want to be ignored as below

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

avrahamb
Contributor II
Contributor II
Author

also not working

Capture.PNG

vinieme12
Champion II
Champion II

NOW,


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

avrahamb
Contributor II
Contributor II
Author

Seems better but not working

it concatenates all of the Refrences instead of the ones with the lowest prices

Capture.PNG

the answer is

Capture.PNG

vinieme12
Champion II
Champion II

have you checked if all of those reference have the same price?

can you upload a sample

Preparing examples for Upload - Reduction and Data Scrambling

avrahamb
Contributor II
Contributor II
Author

sorry file is 45 MB and no, the prices are not the same

..