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

Straight Table - how to get the cheapest vendor reference

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Anonymous — Sun, 22 Jan 2017 07:50:27 GMT

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

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

amirkachlon — Sun, 22 Jan 2017 07:51:44 GMT

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

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

vinieme12 — Sun, 22 Jan 2017 08:08:19 GMT

Now I see what you need, just use the below

=FirstSortedValue(TOTAL <QCD_Component_PN> QCD_QC_Nbr,QCD_Material_Cost)

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

girirajsinh — Sun, 22 Jan 2017 08:27:29 GMT

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

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

Anonymous — Sun, 22 Jan 2017 09:45:45 GMT

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

thanks, Barak

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

vinieme12 — Sun, 22 Jan 2017 10:06:18 GMT

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

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

Anonymous — Mon, 23 Jan 2017 06:16:05 GMT

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

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

Anonymous — Mon, 23 Jan 2017 06:18:37 GMT

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

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

vinieme12 — Mon, 23 Jan 2017 09:32:30 GMT

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

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

Anonymous — Mon, 23 Jan 2017 10:18:38 GMT

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

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

vinieme12 — Mon, 23 Jan 2017 10:20:20 GMT

Can you post a snapshot?

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

vinieme12 — Mon, 23 Jan 2017 10:21:42 GMT

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

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

Anonymous — Mon, 23 Jan 2017 10:25:18 GMT

also not working

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

vinieme12 — Mon, 23 Jan 2017 10:30:22 GMT

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

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

Anonymous — Mon, 23 Jan 2017 12:22:46 GMT

Seems better but not working

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

the answer is

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

vinieme12 — Mon, 23 Jan 2017 13:17:28 GMT

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

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

Anonymous — Mon, 23 Jan 2017 13:40:51 GMT

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