topic Re: How to generate a table that contains the nth occurrence of the value according its row number position? in QlikView

How to generate a table that contains the nth occurrence of the value according its row number position?

Thu, 19 Jan 2017 12:13:37 GMT

Hello everyone,

I know that the Title of this discussion is a little bit big, but let me explain.

Suppose I have a simple list of itens loaded:

Mytable:

Load * inline

[ ITEMS

soda

chocolate

soda

chocolate

banana

chocolate

soda

banana

soda

]

And then I want to obtain the number of the nth occurence of the specific item as long this item occurs again on the list. Below is the result:

(i'm using '-' to delimiter the column)

Nth-ITEMS

1-soda

1-chocolate

2-chocolate

2-soda

3-chocolate

1-banana

4-chocolate

5-chocolate

3-soda

2-banana

4-soda

Explanation:

first line I have 'soda', so its the first occurence of 'soda'

second line I have 'chocolate' and it is the first occurence of 'chocolate' on the list

third line I have 'chocolate' again, and it is the second occurence of 'chocolate'

...and so on

This is very easy to do on Excel, using COUNTIF() function. Using Qlikview I can't get how realize it.

Can you please help me?

Re: How to generate a table that contains the nth occurrence of the value according its row number position?

sunny_talwar — Thu, 19 Jan 2017 12:17:11 GMT

Try this may be:

Mytable:

LOAD *,

AutoNumber(RowNo(), ITEMS) & '-' & ITEMS as [Nth-ITEMS];

Load * inline

[ ITEMS

soda

chocolate

soda

chocolate

banana

chocolate

soda

banana

soda

];

Re: How to generate a table that contains the nth occurrence of the value according its row number position?

Thu, 19 Jan 2017 12:44:45 GMT

OMG!!!

It worked perfectly!!!

*-------*

I'd like to thank you a lot Sunny T

It saved me!!!!