Need help in converting Excel formulae

[Anonymous]

in excel using this formulae, where not sure how to build in Qlik sense;

=IF(AND(A2<>A1,A2<>A3),"Single",IF(A2<>A1,"First",IF(A2<>A3,"Last",IF(A3=A2,"Same",""))))

Name ID	List entry
57136	First
57136	Last
57207	First
57207	Same
57207	Last
57213	First
57213	Same
57213	Last
57214	First
57214	Same
57214	Last
57215	First
57215	Same
57215	Last
57216	First
57216	Same
57216	Last
57219	First
57219	Last
57223	First
57223	Same
57223	Last

[Gysbert_Wassenaar]

Perhaps like this:

tmpList1:

LOAD [Name ID] FROM ...excel_file ...

tmpList2:

LOAD

     [Name ID],

     If(Previous([Name ID] <> [Name ID],'First','Same') AS [List Entry],

     RecNo() as RecNo

RESIDENT

     tmpList1

ORDER BY

     [Name ID];

DROP Table tmpList1;

List:

NOCONCATENATE LOAD

     [Name ID],

     If(Previous([Name ID] <> [Name ID],'Last', [List Entry]) AS [List Entry]

RESIDENT

     tmpList2

ORDER BY

     [Name ID],

     RecNo desc

     ;

DROP Table tmpList1;

[ArnadoSandoval]

Hi Devaraj,

I am including my solution below, which is based on the "Peek()" function, instead of the "Previous()" suggested by Gisbert, mine only required one "temp" table, which is dropped at the end of the script.

RawData:
LOAD
    "Name ID"
FROM [lib://External_Data/Excel_Formula.xlsx]
(ooxml, embedded labels, table is Data);

temp1:
Load
 RowNo() as RowNumber,
    "Name ID"
Resident
 RawData
Order By
 "Name ID";

Drop Table RawData;

formula:
Load
 RowNumber,
    "Name ID",
 if( Peek([Name ID], (RowNumber - 2), 'temp1') <> Peek([Name ID], (RowNumber - 1), 'temp1') and
    Peek([Name ID], (RowNumber - 1), 'temp1') <> Peek([Name ID], RowNumber,       'temp1'), 'Single',
    if( Peek("Name ID", (RowNumber - 2), 'temp1') <> Peek("Name ID", RowNumber - 1, 'temp1'), 'First', 
    if(Peek("Name ID", (RowNumber - 1), 'temp1') = Peek("Name ID", RowNumber, 'temp1'), 'Same', 'Last'))) as Result,
    Peek([Name ID], (RowNumber - 2), 'temp1') as previous,
    Peek([Name ID], (RowNumber - 1), 'temp1') as current,
    Peek([Name ID], RowNumber,       'temp1') as next
Resident
 temp1;

Drop Table temp1;   

Hope this helps,

Arnaldo