topic Re: Fetch the First Not NULL Value in QlikView

Fetch the First Not NULL Value

Thu, 09 Feb 2017 21:52:59 GMT

Hi All,

Hope everybody is good.

Can somebody please help me for the below scenario?

YEAR	SALE
2000
2001
2002
2003	678
2004	243
2005	800
2006	456

I want an expression to fetch the first Year value having NOT NULL value of SALE.

In this case I want 2003.

Your Help is appreciated.

Regards

Srawan

Re: Fetch the First Not NULL Value

Digvijay_Singh — Thu, 09 Feb 2017 21:59:33 GMT

May be like this -

=Min({<SALE={"<>ISNULL()"}>}YEAR)

Re: Fetch the First Not NULL Value

sunny_talwar — Thu, 09 Feb 2017 22:03:16 GMT

May be this:

Sum({<YEAR = {$(=Min({<YEAR = {"=Sum(SALE) > 0"}>}YEAR))}>}SALE)

Re: Fetch the First Not NULL Value

swuehl — Thu, 09 Feb 2017 22:03:49 GMT

If there are really only values for years > 2003, you can try

=Min({<SALE = {"*"}>} YEAR)

Re: Fetch the First Not NULL Value

sunny_talwar — Thu, 09 Feb 2017 22:05:21 GMT

Sample image and app attached

Re: Fetch the First Not NULL Value

vishsaggi — Thu, 09 Feb 2017 22:06:25 GMT

Where do you want this to show?

Extending Vijays's Expression you can also use adding a straight table with Year Dim and Expr as below

= Min({< SALE -= {''}>} SALE)

Re: Fetch the First Not NULL Value

swuehl — Thu, 09 Feb 2017 22:12:24 GMT

That's not returning 2003, Sunny

Srawan, if your SALES values could also be just blank, you could also try

=Min({<SALE = {">0"}>}YEAR)

=Min({<SALE = {"=Len(Trim(SALE))"} >} YEAR)

Re: Fetch the First Not NULL Value

sunny_talwar — Thu, 09 Feb 2017 22:17:12 GMT

I am not sure if the OP needs to just return the year or the sales associated with that year. If the year is needed, you are bang on target

Re: Fetch the First Not NULL Value

swuehl — Thu, 09 Feb 2017 22:22:00 GMT

Yes, could be the case.

Another option for this would be

=FirstSortedValue({<SALE = {">0"}>}SALE, YEAR)

Re: Fetch the First Not NULL Value

Mon, 13 Feb 2017 15:02:30 GMT

Thanks a lot the solution worked.

Re: Fetch the First Not NULL Value

Mon, 13 Feb 2017 15:03:45 GMT

Thanks a lot this worked..