topic Re: Expression Help in QlikView

Expression Help

sujeetsingh — Sat, 11 Oct 2014 06:27:45 GMT

Hello all,

I am having a data level mismatch in a functionality.

I have

ID, Code, First Name, Last Name,Status1, Status2, Date

1 , 22 , A , B , Home , Office , 22/5/2011

1 , 22 , A , B , Park, Shop, 22/7/2011

1 , 22 , A , B , '', Shop, 22/1/2012

1 , 22 , A , B , LANE, Office , 21/5/2012

In a straight table i am using Code, First Name, Last Name, as Dimension

Now i want to calculate ID wise latest Status1 based on date.

Straigh table will have value as

Code | First Name| Code| Status Primary

A | B | 22 | LANE

Refer this thread for details

FirstSortedValue Help!!

Re: Expression Help

MK_QSL — Sat, 11 Oct 2014 07:41:53 GMT

Your expression should be

FirstSortedValue(Status1,-Date)

Please make sure that Date is in proper format, otherwise use below in your script for Date....Otherwise FirstSortedValue will note work...

Date(Date#(Date,'DD/M/YYYY')) as Date

Date(Date#(Date,'DD/MM/YYYY')) as Date

Re: Expression Help

sujeetsingh — Sat, 11 Oct 2014 08:13:10 GMT

Do not work!!

Re: Expression Help

jyothish8807 — Sat, 11 Oct 2014 09:05:14 GMT

Hi Sujeet,

Can you share your sample data?

If you don't have ID and code other than 1 and 22 then,

Firstsorted value suggested my manishkachhia should work.

Regards

Re: Expression Help

sujeetsingh — Sat, 11 Oct 2014 09:26:40 GMT

Guys i am using expression as

=aggr(FirstSortedValue([Status1], -[RowNo()]), ID)

at some places it works fine but at some location it just give the 2nd value before the latest date.

Re: Expression Help

jyothish8807 — Sat, 11 Oct 2014 09:37:42 GMT

Hi Sujeeth,

Then there is an issue with your date format.

Convert them properly.It may help.

To test that, create a text box : =max(date)

Select different ID and check if max Date is the same according to the data.

Regards

Re: Expression Help

MK_QSL — Sat, 11 Oct 2014 10:00:12 GMT

Script

Load

ID,

Code,

[First Name],

[Last Name],

Status1, Status2, Date(Date#(Date,'DD/M/YYYY')) as Date

Inline

[

ID, Code, First Name, Last Name,Status1, Status2, Date

1 , 22 , A , B , Home , Office , 22/5/2011

1 , 22 , A , B , Park, Shop, 22/7/2011

1 , 22 , A , B , '', Shop, 22/1/2012

1 , 22 , A , B , LANE, Office , 21/5/2012

];

Now follow my previous post for steps

Re: Expression Help

sujeetsingh — Sat, 11 Oct 2014 10:40:19 GMT

That is all going good

Re: Expression Help

sujeetsingh — Tue, 14 Oct 2014 04:09:25 GMT

Thanks All for you replies.

The issue i was facing in some ID is due to Sorting of the data based on Date.