How to String Compare?

[chengyu]

Hi Sir:
My Raw data CurrentPrice,VendorPrice1...is string not number, so i use convert change fields attribute. I hope VendorPrice1 < CurrentPrice or VendorPrice2 < CurrentPrice or VendorPrice3 < CurrentPrice appear data and highlights red color. If use ... |search VendorPrice1 < 0.3345 | ... is fine, if use |search VendorPrice1 < CurrentPrice| data is error, It can not seem to use than the size of the string, should can i do? Thank you.

My RAW data:
CurrentPrice VendorPrice1 VendorPrice2 VendorPrice3
.3345 .3303 .3302 .3305

sourcetype=xxx Status=Approved PartNo=$PartNo$ VendorCode=$VendorCode$ | Convert num(CurrentPrice) as CurrentPrice,num(VendorPrice1) as VendorPrice1,num(VendorPrice2) as VendorPrice2,,num(VendorPrice3) as VendorPrice3 | search ((VendorPrice1 < CurrentPrice) OR (VendorPrice2 < CurrentPrice) OR (VendorPrice3 < CurrentPrice) ) |stats values(CurrentPrice) values(VendorPrice1) values(VendorPrice2) values(VendorPrice3) by RfqNo,PartNo,VendorCode

[chengyu]

Hi Runals:

         I have try where command | where VendorPrice1 < CurrentPrice |, result still  the same. 

[chengyu]

I misplaced where command. Sorry, already resolve.

[stephanefotso]

Hello! When you use Values (X), The order of the values is lexicographical. Try the list function as follows and let me know what happen:

sourcetype=xxx Status=Approved PartNo=$PartNo$ VendorCode=$VendorCode$ | Convert num(CurrentPrice) as CurrentPrice,num(VendorPrice1) as VendorPrice1,num(VendorPrice2) as VendorPrice2,,num(VendorPrice3) as VendorPrice3 | search ((VendorPrice1 < CurrentPrice) OR (VendorPrice2 < CurrentPrice) OR (VendorPrice3 < CurrentPrice) ) |stats list(CurrentPrice) list(VendorPrice1) list(VendorPrice2) list(VendorPrice3) by RfqNo,PartNo,VendorCode

[Runals · Accepted solution]

In your case since you are comparing values in a field what you want to use is where not search as in

... | where VendorPrice1 < CurrentPrice | ...

The search command like you've used it WILL work if you put in values as you've seen

[vganjare]

Is there any value of the CurrentPrice which is lesser than any of the VendorPrices? In current example, the current price value .3345 is largest among all the prices.

Thanks!!