topic Re: Serial Number on Dimension in QlikView

Serial Number on Dimension

noman212 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends,

How can i add Serial number based on multiple dimension.

i want to do this on calculated dimension bases in a pivot table.

Awaiting your reply.

Regards

Noman khan

Re: Serial Number on Dimension

ajsjoshua — Wed, 18 May 2016 05:23:45 GMT

Hi,

use

=rowno() or rowno(total).

Regards,

Joshua.

Re: Serial Number on Dimension

ajsjoshua — Wed, 18 May 2016 05:26:10 GMT

Hi,

check this Row Counter in Chart

Re: Serial Number on Dimension

Chanty4u — Wed, 18 May 2016 05:26:22 GMT

Am not sure

Do you mean RowNo() and RecNo()?

RecNo( )

Returns an integer for the number of the currently read row of the source data table. The first record is

number 1.

RowNo( )

Returns an integer for the position of the current row in the resulting QlikView internal table. In contrast to

RecNo( ), which counts the records in the raw data table, the RowNo( ) function does not count records that

are excluded by where clauses and is not reset when a raw data table is concatenated to another. The first

row is number 1.

Re: Serial Number on Dimension

noman212 — Wed, 18 May 2016 05:31:19 GMT

Not working.

i have 3 dimensions

Category , Brand , and SKU..

Category = Perfume

Brand = body spray

= ABC

= XYZ

BODY SPRAY's SKU

SKU =

Re: Serial Number on Dimension

ajsjoshua — Wed, 18 May 2016 05:32:32 GMT

Hi,

rowno(total) should work for multiple dimensions .

if not provide some sample in excel.

Re: Serial Number on Dimension

noman212 — Wed, 18 May 2016 05:32:57 GMT

SCENERIO:

i have 3 dimensions

Category , Brand , and SKU..

Category = Perfume

Brand = body spray

= ABC

= XYZ

BODY SPRAY's SKU

SKU =

i want serial no. on SKU based on Brand

Re: Serial Number on Dimension

Wed, 18 May 2016 05:38:45 GMT

Try at script level & create like below,

Load *, autonumber(SKU) as SerialNo resident source;

Re: Serial Number on Dimension

Kushal_Chawda — Wed, 18 May 2016 05:42:26 GMT

Create the Expression as

=Rowno(Total)

Re: Serial Number on Dimension

ajsjoshua — Wed, 18 May 2016 05:44:14 GMT

Hi,

achieved both in exp and script

PFA

Re: Serial Number on Dimension

noman212 — Wed, 18 May 2016 05:50:37 GMT

1 - How can i placed Expression in Column no 1 in pivot table?

2 - =Rowno(Total) gives serial 1 to N number of SKU.

But, i want it on Brand wise like Brand ABD has 5 SKU so serial no of brand's ABD SKU is 1,2,3,4,5

BRAND XYZ has 2 SKU so serial no of brand's XYZ SKU is 1,2. and so on....

Re: Serial Number on Dimension

Kushal_Chawda — Wed, 18 May 2016 05:59:53 GMT

So try it in script Like below

Data:

LOAD Brand,

SKU

FROM Table.qvd(qvd);

New:

noconcatenate

LOAD *,

if(rowno()=1 or Brand<>Previous(Brand),1,

if(Brand = previous(Brand) , peek('Rank')+1) as Rank

resident Data

order by Brand,SKU;

drop table Data;

Re: Serial Number on Dimension

ajsjoshua — Wed, 18 May 2016 06:02:37 GMT

Hi,

check this rowno in table (am I using the right function?) | Qlik Community

Re: Serial Number on Dimension

sunny_talwar — Wed, 18 May 2016 10:19:23 GMT

Using the sample attached by joshua‌ (Thanks)

For dimension, you can also use this:

=Aggr(RowNo(), sku)

Right now the RowNo() will be assigned based on load order of sku, but if you are using QV12 and would want to sort sku in a particular order you can sort the sku within the Aggr() function

The sortable Aggr function is finally here!‌