Inter-record count

[#1]

Hello,

I have a question for a scenario I cannot solve.

I would like to count how many times a record from table 1 went back from status 3 to status 2.

In case of item A for example, this is 3 times.

In case of item B for example, this is 2 times.

Thx in advance for any help!

Table 1	Table 2 - Date	Table 2 - Status
A	1	Status 1
A	2	Status 2
A	3	Status 3
A	4	Status 2
A	4	Status 2
A	5	Status 3
A	7	Status 2
A	8	Status 3
A	9	Status 4
B	1	Status 1
B	2	Status 2
B	3	Status 3
B	4	Status 2
B	5	Status 2
B	6	Status 2
B	7	Status 2
B	8	Status 3
B	9	Status 4

[maxgro · Accepted solution]

str table chart with expression

len(keepchar(replace('-' & concat([Table 2 - Status], '-', [Table 2 - Date]) & '-', 'Status 2-Status 3-', '@'), '@'))

script

t:

LOAD [Table 1], [Table 2 - Date], [Table 2 - Status]

FROM [http://community.qlik.com/thread/128701] (html, codepage is 1252, embedded labels, table is @1);

[#3]

Inspiration sometimes comes at the end of the workingday.

I just found out that the Above() function can be used to compare two records.

So far, so good. Now just counting them.

I will try out your solution as well Massimo.

Thanks a lot already for the reaction!

[#4]

Thank you Massimo.

Nice approach to my problem! Will remember that for the future.