topic Re: Find the Latest Value by date from a Table_Qliksense in QlikView

Find the Latest Value by date from a Table_Qliksense

rpavan17 — Tue, 22 May 2018 07:22:45 GMT

Hi,

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

Unique_ID,

Index,

TimeStamp

Index

TimeStamp

Orange

22/05/2017 12:30:00

Orange

22/04/2017 14:30:00

Apple

15/02/2017 6:30:00

Apple

16/03/2017 8:00:00

Mango

21/05/2017 8:30:00

Mango

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

Re: Find the Latest Value by date from a Table_Qliksense

kaushiknsolanki — Tue, 22 May 2018 07:58:38 GMT

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/BasicAggregationFunctions/firstsor…

Regards,

Kaushik Solanki

Re: Find the Latest Value by date from a Table_Qliksense

Anonymous — Tue, 22 May 2018 08:01:05 GMT

Hye,

try

=FirstSortedValue(Index, -Timestamp)

Re: Find the Latest Value by date from a Table_Qliksense

rpavan17 — Tue, 22 May 2018 08:11:01 GMT

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

Re: Find the Latest Value by date from a Table_Qliksense

Anonymous — Tue, 22 May 2018 08:17:59 GMT

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 ?

Re: Find the Latest Value by date from a Table_Qliksense

rpavan17 — Tue, 22 May 2018 08:50:14 GMT

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

Re: Find the Latest Value by date from a Table_Qliksense

Anonymous — Tue, 22 May 2018 08:53:37 GMT

can you post your code ?

Re: Find the Latest Value by date from a Table_Qliksense

rpavan17 — Tue, 22 May 2018 08:56:29 GMT

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

Re: Find the Latest Value by date from a Table_Qliksense

Anonymous — Tue, 22 May 2018 09:29:20 GMT

I dont really understand your goal.

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

Re: Find the Latest Value by date from a Table_Qliksense

rpavan17 — Wed, 23 May 2018 06:56:48 GMT

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

Re: Find the Latest Value by date from a Table_Qliksense

Anonymous — Wed, 23 May 2018 07:10:33 GMT

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.

Re: Find the Latest Value by date from a Table_Qliksense

rpavan17 — Wed, 23 May 2018 10:30:25 GMT

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.

Re: Find the Latest Value by date from a Table_Qliksense

rpavan17 — Wed, 23 May 2018 10:45:44 GMT

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.

Re: Find the Latest Value by date from a Table_Qliksense

Anonymous — Wed, 23 May 2018 11:50:04 GMT

i think you need a group by unsing FirstSortedValue on load

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/AggregationFunctions/firstsorted.h…

something like this

Load

ID,

FirstSortedValue(INDEX,-R_C_Timestamp) as R_C_Index,

INDEX

group by Index