Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Find last occurrence of value in script

Hi everybody,

I saw this example at FieldIndex - script and chart function ‒ Qlik Sense :

Names:

LOAD * inline [

"First name"|"Last name"|Initials|"Has cellphone"

John|Anderson|JA|Yes                              // #1

Sue|Brown|SB|Yes                              // #2

Mark|Carr|MC |No                              // #3

Peter|Devonshire|PD|No                              // #4

Jane|Elliot|JE|Yes                              // #5

Peter|Franc|PF|Yes ] (delimiter is '|');                              // #6

John1:

Load FieldIndex('First name','John') as MyJohnPos    //This gives 1

Resident Names;

Peter1:

Load FieldIndex('First name','Peter') as MyPeterPos   //This gives 4

Resident Names;

My question is the following: is there a way to find the index (or directly find the value) of the last occurrence of the name? What I'd like to say is wheter there's a way to have MyPeterPos = 6 instead of 4.

Thanks a lot in advance,

best regards,

Nicolò Cogno

1 Solution

Accepted Solutions

Re: Find last occurrence of value in script

It depends on exactly how you want to use this.  Here are a couple of approaches:

Names:

LOAD *,RecNo() as index inline [

"First name"|"Last name"|Initials|"Has cellphone"

John|Anderson|JA|Yes                            

Sue|Brown|SB|Yes                            

Mark|Carr|MC |No                            

Peter|Devonshire|PD|No                            

Jane|Elliot|JE|Yes                            

Peter|Franc|PF|Yes

] (delimiter is '|');                            

// Preload a table of everyone's last index

FindIndex:

LOAD

LastValue(index) as LastIndex,

[First name]

Resident Names

Group By [First name]

;

LET vPeterLast = Lookup('LastIndex', 'First name', 'Peter', 'FindIndex');

// Or reverse the raw table and lookup names on demand.

NamesReversed:

NoConcatenate

LOAD * Resident Names

Order by index DESC;

DROP TABLE Names;

LET vPeterLast2 = Lookup('index', 'First name', 'Peter', 'NamesReversed');


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

7 Replies
Employee
Employee

Re: Find last occurrence of value in script

Please try this

Names:

LOAD *,RecNo() as index inline [

"First name"|"Last name"|Initials|"Has cellphone"

John|Anderson|JA|Yes                              // #1

Sue|Brown|SB|Yes                              // #2

Mark|Carr|MC |No                              // #3

Peter|Devonshire|PD|No                              // #4

Jane|Elliot|JE|Yes                              // #5

Peter|Franc|PF|Yes ] (delimiter is '|');                              // #6

John1:

Load FieldIndex('First name','John') as MyJohnPos    //This gives 1

Resident Names;

Peter1:

Load FieldIndex('First name','Peter') as MyPeterPos   //This gives 4

Resident Names;

load "First name", max(index)  as maxindex Resident Names Group by "First name";

Not applicable

Re: Find last occurrence of value in script

Hi Clever and thanks for your reply,

that was just an example, I've got a bigger algorithm that load rows from a huge table and I'd like to find a function (or a composition of functions) to perform that task. Do you know if exist something like that?

Thanks again,

best regards,

Nicolò Cogno                   

Employee
Employee

Re: Find last occurrence of value in script

Any problem if you use this approach? Have you tested?

Not applicable

Re: Find last occurrence of value in script

It works, but it's a "standalone method". I'd like to have something that stands inside 3 nested if statements and gives me the value on the line

stalwar1‌ Do you know anything like that?

Thanks again

Nicolò Cogno

MVP
MVP

Re: Find last occurrence of value in script

I don't think FieldIndex can handle this, but may be an alternative like what cleveranjos‌ pointed out... but it might be easier to see why his solution doesn't work if you can provide a more detailed explanation of what you are looking to do

Re: Find last occurrence of value in script

It depends on exactly how you want to use this.  Here are a couple of approaches:

Names:

LOAD *,RecNo() as index inline [

"First name"|"Last name"|Initials|"Has cellphone"

John|Anderson|JA|Yes                            

Sue|Brown|SB|Yes                            

Mark|Carr|MC |No                            

Peter|Devonshire|PD|No                            

Jane|Elliot|JE|Yes                            

Peter|Franc|PF|Yes

] (delimiter is '|');                            

// Preload a table of everyone's last index

FindIndex:

LOAD

LastValue(index) as LastIndex,

[First name]

Resident Names

Group By [First name]

;

LET vPeterLast = Lookup('LastIndex', 'First name', 'Peter', 'FindIndex');

// Or reverse the raw table and lookup names on demand.

NamesReversed:

NoConcatenate

LOAD * Resident Names

Order by index DESC;

DROP TABLE Names;

LET vPeterLast2 = Lookup('index', 'First name', 'Peter', 'NamesReversed');


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Not applicable

Re: Find last occurrence of value in script

cleveranjosstalwar1rwunderlich‌ thank you guys for your help, as you suggested to me I firstly did a preliminary load of the table and then I used a combination of Lookup funcion, variables, RowNo and fieldindex to get everything work.

Thanks again,

best regards,

Nicolò Cogno

Community Browser