Find last occurrence of value in script

[#1]

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

[Clever_Anjos]

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";

[#3]

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                   

[Clever_Anjos]

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

[#5]

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

[sunny_talwar]

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

[rwunderlich · Accepted solution]

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

[#8]

cleveranjos‌ stalwar1‌ rwunderlich‌ 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