Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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