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!!!

[kaushiknsolanki]

Hi,

What you need is firstsortedvalue() function.

Check below links.

http://www.learnqlikview.com/first-sorted-value-function-in-qlikview/

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/BasicAggregati...

Regards,

Kaushik Solanki

[Anonymous · Accepted solution]

Hye,

try

=FirstSortedValue(Index, -Timestamp)

[rpavan17]

Hi,

If i give

=FirstSortedValue(ACTION_INDEX,Time_stamp)

It shows the first date index value.It has to show latest index.

=FirstSortedValue(ACTION_INDEX,-Time_stamp). It results '-'

Thanks

[Anonymous]

What i've done :

Table_Test:

LOAD * INLINE [

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 6:30:00

];

Dimension : ID

Measure : =FirstSortedValue(Index,-Timestamp)

and works fine ..

Are you sure you dont have null value in your date or index ?

[rpavan17]

Hi..

Thank You for your answer. It looks like working when i give separate expression.

I am trying to add this in if  with other conditions.It gives error as Invalid Dimension.

Please help

[Anonymous]

can you post your code ?

[rpavan17]

Hi,

It is

if( R_NAME = 'M_USER' AND FirstSortedValue(INDEX,-TIMESTAMP) ='3' ,'ForCancellation')

In Dimension formula, It is OK But when i click Apply, it is Invalid Dimension

[Anonymous]

I dont really understand your goal.

Can you provide sample data or sample application to work on?

[rpavan17]

Hi,

I have give this in load script

Load

FirstSortedValue( INDEX,-Time_stamp) as R_C_Index

Resident DOCS;

It gives correct value if there are two values (3,4)for One ID. It gives 3.

But, If a ID contains value 4, then also it shows 3.

It looks like for any id, it shows 3 is the value of R_C_Index