topic row number only where an expression is not null in QlikView

row number only where an expression is not null

Mon, 08 Dec 2014 08:28:27 GMT

hi, I am trying to get row numbers only for those rows in a pivot table where an expression is not null. For eg:
if my table has following values

value

1200

123

120

232

I want the row numbers in a adjacent column to be displayed as:

value rownumber

1200 1

123 2

120 3

232 4

and so on.. any suggestions?

Re: row number only where an expression is not null

jonathandienst — Mon, 08 Dec 2014 08:33:59 GMT

Null or 0? Your question and example do not tie up.

Assuming null - when loading the table containing value:

LOAD ...

value,

If(IsNull(value) = 0, 1 + Alt(Peek(RowNo), 0)) As RowNo,

...

HTH

Jonathan

Re: row number only where an expression is not null

Mon, 08 Dec 2014 08:37:53 GMT

sorry for the ambiguity in my question. It's 0 and not null and I want to implement this in the front end only, not the script.

Re: row number only where an expression is not null

Mon, 08 Dec 2014 10:39:27 GMT

You check on Suppress Zero Values in the Presentation tab which would remove the dimensions that hold a 0 as a value and then use RowNo()

Re: row number only where an expression is not null

simenkg — Mon, 08 Dec 2014 10:43:26 GMT

Table:
Load

Value,

rowno() as RowNo

form Table.qvd (qvd)

where Value<>'0';

Concatenate(Table)
load Value

From Table.qvd (qvd)

where Value = 0;

Re: row number only where an expression is not null

anbu1984 — Mon, 08 Dec 2014 10:59:31 GMT

Check this app

Re: row number only where an expression is not null

anbu1984 — Mon, 08 Dec 2014 11:05:36 GMT

If you have two dimensions, then try this

Re: row number only where an expression is not null

Mon, 08 Dec 2014 11:18:22 GMT

Hi,

Try this....

LOAD * INLINE [

A, B

a, 0

c, 12

c, 0

e, 4

f, 10

g, 0

h, 156

];

Rank(count(total A)+if(sum(B)>0,Rank(if(sum(B)>0,RowNo()))))

Regards,

Garry

Re: row number only where an expression is not null

Anonymous — Tue, 23 Aug 2016 02:50:33 GMT

Hi Cheliyan,

Thanks for the answer. I get my answer from your example.

Thanks.

Best Regards,