topic Show the Last Value from Field in a Table in QlikView

Show the Last Value from Field in a Table

Tue, 17 Jun 2014 15:03:04 GMT

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

Re: Show the Last Value from Field in a Table

its_anandrjs — Tue, 17 Jun 2014 15:16:03 GMT

In the straight table

Dimension:- ACCOUNT_ID

Expression:- FirstSortedValue(INSPECTION_DATE, AMOUNT)

Then you get latest date for the ACCOUNT_ID.

Re: Show the Last Value from Field in a Table

eduardo_sommer — Tue, 17 Jun 2014 15:21:10 GMT

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

Re: Show the Last Value from Field in a Table

Tue, 17 Jun 2014 15:24:46 GMT

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

Re: Show the Last Value from Field in a Table

ashfaq_haseeb — Tue, 17 Jun 2014 15:28:30 GMT

Hi try below

Dimension--> ACCOUNT_ID

Expression--> FirstSortedValue(AMOUNT, -INSPECTION_DATE)

Re: Show the Last Value from Field in a Table

its_anandrjs — Tue, 17 Jun 2014 15:30:49 GMT

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

Re: Show the Last Value from Field in a Table

its_anandrjs — Tue, 17 Jun 2014 15:32:22 GMT

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

Re: Show the Last Value from Field in a Table

MK_QSL — Tue, 17 Jun 2014 15:37:14 GMT

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;

=======

Re: Show the Last Value from Field in a Table

eduardo_sommer — Tue, 17 Jun 2014 15:39:53 GMT

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

Re: Show the Last Value from Field in a Table

its_anandrjs — Tue, 17 Jun 2014 15:52:23 GMT

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

Re: Show the Last Value from Field in a Table

its_anandrjs — Tue, 17 Jun 2014 15:53:53 GMT

Hi Manish,

Yes it is right by AMOUNT the date is not obtain because in data the amount should be same then it is not worked, thanks for identify.

Regards

Re: Show the Last Value from Field in a Table

its_anandrjs — Tue, 17 Jun 2014 15:58:17 GMT

Hi Eduardo,

By AMOUNT in firstsortedvalue is not good enough i change the script and use Max and Min because in data the amount should be same in some condition then not worked Max and Min should be the right one. I wanted to do it with the Max and Min but suddenly use Firstsortedvalue. For Amount it is correct FirstSortedValue(INSPECTION_DATE, AMOUNT).

Regards

Anand