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

[#1]

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

  chocolate

  soda

  chocolate

  banana

  chocolate

  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?

[sunny_talwar · Accepted solution]

Try this may be:

Mytable:

LOAD *,

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

Load * inline

[ ITEMS

  soda

  chocolate

  chocolate

  soda

  chocolate

  banana

  chocolate

  chocolate

  soda

  banana

  soda

];

[#3]

OMG!!!

It worked perfectly!!!

*-------*

I'd like to thank you a lot Sunny T

It saved me!!!!