Straight Table - how to get the cheapest vendor reference

[Anonymous]

Hello,

i've got this kind of data set:

QCD_Material_Cost (Per Unit)	QCD_QC_Nbr	QCD_Component_PN	Lowest Quotes Price	Quotation nbr
			52	4452590
52	4452590	0100-2510	52	4452590
97	4459110	0100-2510	52
256	4458800	0100-2510	52
366	4456651	0100-2510	52
386	4458790	4985-4444	52	4458790
561	4453870	4985-4444	52

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_Nbr	QCD_Component_PN	Lowest Quotes Price	Quotation nbr
			52	4452590
52	4452590	0100-2510	52	4452590
97	4459110	0100-2510	52	4452590
256	4458800	0100-2510	52	4452590
366	4456651	0100-2510	52	4452590
386	4458790	4985-4444	52	4458790
561	4453870	4985-4444	52	4458790

Thanks, Barak

[Anonymous]

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

[vinieme12]

Can you post a snapshot?

[vinieme12]

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

[Anonymous]

also not working

[vinieme12]

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

[Anonymous]

Seems better but not working

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

the answer is

[vinieme12]

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

[Anonymous]

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

..