Show the Last Value from Field in a Table

[#1]

Hello!

I have a Simple Table with:

• ACCOUNT_ID
• INSPECTION_DATE
• AMOUNT

1 Account_Id could have 2 Inspection_Date.

What I want is to show in this Simple Table the last Inspection_Date for the Account_id.

Do you know how could I do that?

Thank you!!!

[its_anandrjs]

In the straight table

Dimension:- ACCOUNT_ID

Expression:- FirstSortedValue(INSPECTION_DATE, AMOUNT)

Then you get latest date for the ACCOUNT_ID.

[eduardo_sommer]

Hi Anand,

I think the order of the parameters in the function is inverted and missing the - sign. It should be:

     =FirstSortedValue(AMOUNT, -INSPECTION_DATE)

Eduardo

[#4]

Do I have to put the minus sign in the Dimension/Expression?

[ashfaq_haseeb]

Hi try below

Dimension--> ACCOUNT_ID

Expression--> FirstSortedValue(AMOUNT, -INSPECTION_DATE)

[its_anandrjs]

No i believe it is for the first inspection date for that please see this small example

Tab1:

LOAD ACCOUNT_ID,Date#(INSPECTION_DATE,'DD/MM/YYYY') AS INSPECTION_DATE,AMOUNT;

LOAD * Inline

[

ACCOUNT_ID,INSPECTION_DATE,AMOUNT

1,14/06/2014,158742

1,15/06/2014,021212

1,16/06/2014,15847

2,12/04/2014,158256

2,15/06/2014,45821

3,12/04/2014,285862

3,16/03/2014,45823

3,15/05/2014,48526

4,12/04/2014,25886

];

Then Use this Dimension and Measure

Dimension:- ACCOUNT_ID

Expression:- FirstSortedValue(INSPECTION_DATE, AMOUNT)

The output is

ACCOUNT_ID	INSPECTION_DATE
1	16/06/2014
2	15/06/2014
3	16/03/2014
4	12/04/2014

[its_anandrjs]

What output you required below is example

ACCOUNT_ID	INSPECTION_DATE
1	16/06/2014
2	15/06/2014
3	16/03/2014
4	12/04/2014

[MK_QSL]

REF: Anand Chouhan Data :

Use below in your script and you will get the first and last inspection date. Use whatever date you want ....

=======

Tab1:

LOAD

  ACCOUNT_ID,

  Date#(INSPECTION_DATE,'DD/MM/YYYY') AS INSPECTION_DATE,

  AMOUNT;

LOAD * Inline

[

  ACCOUNT_ID,INSPECTION_DATE,AMOUNT

  1,14/06/2014,158742

  1,15/06/2014,021212

  1,16/06/2014,15847

  2,12/04/2014,158256

  2,15/06/2014,45821

  3,12/04/2014,285862

  3,16/03/2014,45823

  3,15/05/2014,48526

  4,12/04/2014,25886

];

Join

Load

  ACCOUNT_ID,

  Date(Max(INSPECTION_DATE)) as Last_INSPECTION_DATE,

  Date(Min(INSPECTION_DATE)) as First_INSPECTION_DATE

Resident Tab1

Group By ACCOUNT_ID;

=======

[eduardo_sommer]

Anand,

Your expression is returning the inspection date with the least Amount.

What Mart needs is the last inspection date, which is the result of max(INSPECTION_DATE). If he also wants the amount of that inspections, the function FirstSortedValue(AMOUNT, -INSPECTION_DATE) will give him the right result.

Eduardo

[its_anandrjs]

Hi Mart,

Use the script like below scripts and load another table for latest date with the simple example.

Tab1:

LOAD ACCOUNT_ID,Date#(INSPECTION_DATE,'DD/MM/YYYY') AS INSPECTION_DATE,AMOUNT;

LOAD * Inline

[

ACCOUNT_ID,INSPECTION_DATE,AMOUNT

1,14/06/2014,158742

1,15/06/2014,021212

1,16/06/2014,15847

2,12/04/2014,158256

2,15/06/2014,45821

3,12/04/2014,285862

3,16/03/2014,45823

3,15/05/2014,48526

4,12/04/2014,25886

];

NewDetails:

LOAD

ACCOUNT_ID,

Date(Min(INSPECTION_DATE),'DD/MM/YYYY') as EarliestDate,

Date(Max(INSPECTION_DATE),'DD/MM/YYYY') as LatestDate

Resident Tab1

Group By ACCOUNT_ID;

And in the Table box Plot

ACCOUNT_ID,EarliestDate,LatestDate

Let me know if having problem

Hope this helps