FirstSortedValue

[#1]

Hi all,

Is there a way to get First non zero sorted value?

Month	Data
Jan	100
Feb	100
Mar	300
Apr	600
May	100
Jun	700
Jul	200
Aug	0
Sep	0
Oct	0
Nov	0
Dec	0

If the data is as above and my expression is FirstSortedValue(Data,-Month) i would get 0.

But i want 200 which is the firstNONZEROsortedvalue from the last.

Can i get some help here?

[kaushiknsolanki · Accepted solution]

Hi,

     Why dont you load the non zero values and then find the first sorted value.

     Something like this.

     Noconcatenate

     Load *,Firstsortedvalue(Data,-Month) as Value resident Data where Data <> 0;

     Drop table Data;

Regards,

Kaushik Solanki

[whiteline]

Hi.

It seems you could just use set analysis to calculate FirstSortedValue over the records where Data<>0.

Something like: FirstSortedValue({<Data={'=Data>0'}>} Data,-Month)

[#4]

Hi Whiteline,

Your solution works for the sample data i provided.But, i had lot of other calculations in the actual scenario.Did not work in my cse.

Thanks for your reply and your time:)

Thanks and Regards,

Pavan