topic Simple: How to get running numbers in QlikView

Simple: How to get running numbers

Fri, 16 Apr 2010 04:23:30 GMT

Dear All,
I new in qlikview programing, I have simple question, how do I get running number for the result and total found
Sample below:

No Name Address
1 John Doe Villa No.23
2 John Smith Ave No.32
3 James Smith Road No.2

Total found: 3

Thanks
Frenky

Simple: How to get running numbers

vupen — Fri, 16 Apr 2010 05:54:21 GMT

You can use the built in QV function - RecNo( ). It gives the Record Number, which is sequential.

Ex:


Load
<h4>RecNo( ) as SNo,</h4>
Name,
Address
;
SQL SELECT
Name,
Address
From TABLE_NAME
;

Simple: How to get running numbers

pover — Fri, 16 Apr 2010 05:54:56 GMT

Hi Frenky,

I'm not sure exactly what you're looking for, but if you want to create a column like 'No' then you can used the RowNo() function.

Note that the RecNo() function may not necessarily be conscutive because RecNo() will return the row number the record was found in the original database.

For example:

Original Table:

Name, City
James, London
Erik, Lund
Alice, London

Script:

Load RowNo() as No_1,
RecNo() as No_2,
Name,
City
From 'Orginal Table'
Where City = 'London'

Result:
1, 1, James, London
2, 3, Alice, London

I hope that's clear.

If you want to find the total number found, why don't you use count(distinct Name)?

Regards.

Simple: How to get running numbers

d_pranskus — Fri, 16 Apr 2010 05:57:12 GMT

See example attached

Cheers

Darius

Simple: How to get running numbers

Fri, 16 Apr 2010 06:16:43 GMT

hi,

you can use

load rowno() as no or load recno() as no from ......... as suggested by upendra in your script

and for total found you can use

let total found =noofrows('tablename');

thanks

Simple: How to get running numbers

Fri, 16 Apr 2010 08:22:01 GMT

Hi guys,
Thank you all for replying my question.Formula approach for "rowno()" by tauqueer almost hit my question. (image1 below) BUT when I do a filter is doesn't do correctly (image2 below).
Instead showing the perspective number of user, which this not required because I already have EE (employee number).
Basically i want is to find how many total the result of employee if I do a filter.
And I still confuse how to create TOTAL FOUND.

Simple: How to get running numbers

Fri, 16 Apr 2010 08:47:45 GMT

hi,

you can use

getpossiblecount(Employeename) to know how many employees have been terminated(ie no of employees in the current selection)

and getexcludedcount(Employeename) to know how many employees have been excluded from the current selection

as expressions in your straight table

thanks

Simple: How to get running numbers

Fri, 16 Apr 2010 09:50:24 GMT

Dear Tauqueer,
i'm not sure the formula where can i put in. it become like these below

Simple: How to get running numbers

bismart — Fri, 16 Apr 2010 10:28:47 GMT

Just create counter field with value of 1 and use sum(counterField) in your expressions...

Load

1 as CounterField,

Name,

Address

;

SQL SELECT

Name,

Address

From TABLE_NAME

;

Simple: How to get running numbers

Sat, 17 Apr 2010 01:15:52 GMT

Hi Karl & all,
Thanks replying my question.
It seem both rowno() and recno() has same function, and this is close formula.
Basicly what i want is just running number, l those like in excel.
But i do not want the running number taken from database.
I tried both give the result same when do a filter the running number become number from database, which we not it that.
for the total number found it seem give me different result when i do fiter, please check my screen shoot on No_1[rowno()] and No_2 [recno()]

Anyway for total found using count(No_1) or count(No_2) it seem was the correct formula to get how many the result.

Can anyone help me on this?

Simple: How to get running numbers

pover — Sat, 17 Apr 2010 02:22:28 GMT

Hi Frenky,

RecNo() and RowNo() will be different if you apply the filter in the script with a where statement. If you want dynamic row numbersin a table then you should use the rowno() function as an expression in the straight table.

See attached example.

Good luck

Simple: How to get running numbers

Sat, 17 Apr 2010 16:23:09 GMT

Hi Karl,

Your sample fix my problem 🙂

But can wee use total found on the bottom of table? is should be easier to see if showing at below table

I no idea which table model should i use it.

Simple: How to get running numbers

pover — Sat, 17 Apr 2010 17:30:50 GMT

In the expression tab set the total mode as displayed in screenshot below for the column that has rowno().

Regards.

Simple: How to get running numbers

pover — Sat, 17 Apr 2010 17:33:48 GMT

And if you want the total to appear at the bottom of the table, that option is in the Presentation tab.

Regards.