Find the Latest Value by date from a Table_Qliksense

[rpavan17]

Hi,

Can you help in getting the latest Index Value from a Table contains

Unique_ID,

Index,

TimeStamp

ID	Index	TimeStamp
Orange	3	22/05/2017 12:30:00
Orange	4	22/04/2017 14:30:00
Apple	3	15/02/2017 6:30:00
Apple	4	16/03/2017 8:00:00
Mango	3	21/05/2017 8:30:00
Mango	4	21/05/2017 9:00:00

I have seen other discussions as well. But, i could not get the solution.

Expected Solution:

Orange = Index is 3

Apple = Index is 4

Mango= Index is 4

Thanks!!!

[Anonymous]

Hi,

if i try with this :

Table_Test:

LOAD * INLINE [

ID, Index, Timestamp

Orange , 3 , 22/05/2017 12:30:00

Orange , 4 , 22/04/2017 14:30:00

Orange , 5 , 23/05/2017 13:30:00

Orange , 6 , 23/04/2017 15:30:00

Apple , 3 , 15/02/2017 6:30:00

Apple , 4 , 16/03/2017 6:30:00

Apple , 8 , 16/03/2017 6:31:00

];

it seems to be right (apple 8 orange 5)

I dont really understand without your apps.

[rpavan17]

I am trying to explain clearly. My qvf is very big in size. Cannot upload

Table1:

Load

ID,

Status

From XXXXXX;

Table2:

LOAD

     ID,

    SENDER_NAME,

    SENDED_TO_TIMESTAMP,

Timestamp(SENDED_TO_TIMESTAMP, 'MM-DD-YYYY HH.MM.SS') AS R_C_Time_stamp,

INDEX,

ACTION_NAME

FROM XXXXXXX

Left Join(Table1)

Load

ID,

FirstSortedValue(INDEX,-R_C_Timestamp) as R_C_Index,

INDEX,

Resident Table2;

Drop Table Table2;

Table 3:

Left Join(Table1)

Load

ID,

If(Status='Open' and R_C_Index = '3','Cancelled') as Status_Name

Resident Table 1;

This is how my script looks.

I am trying this.

[rpavan17]

It gives Invalid Expression Error from

Load

ID,

FirstSortedValue(INDEX,-R_C_Timestamp) as R_C_Index,

INDEX,

Resident Table2;

If I remove

FirstSortedValue(INDEX,-R_C_Timestamp) as R_C_Index,

line, it runs.

[Anonymous]

i think you need a group by unsing FirstSortedValue on load

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/AggregationFun...

something like this

Load

ID,

FirstSortedValue(INDEX,-R_C_Timestamp) as R_C_Index,

INDEX

group by Index