topic Re: Max date in QlikView

Max date

Wed, 19 Dec 2012 23:09:18 GMT

Hi,

I have data as below

Emp Date Sal Grade

ABC 01/27/2012 100 A

ABC 02/27/2012 200 B

DEF 3/31/2012 100 B

DEF 5/31/2012 400 C

My requirement is I have to pick Grades and salaries of only latest date for each employee and show it in pie chart...

so ideally I should only pick salaries and grades of employee of ABC Dated 2/27/12 and 5/31/2012 for DEF

currently Im using below formula in pie chart..

sum(salary)/sum(total slalary) so this forumla picks salaries for all records..I want this to pick only salaries and grades of recent dates for each emp and do the calcuation

any help is highly appreciated..

Re: Max date

nagaiank — Wed, 19 Dec 2012 23:26:00 GMT

The script below will work. The table Tab2 has the salary and grades of employees for the latest date.

Tab1:

LOAD * Inline [

Emp,Date,Sal,Grade

ABC,01/27/2012,100,A

ABC,02/27/2012,200,B

DEF,3/31/2012,100,B

DEF,5/31/2012,400,C

];

Tab2:

load Emp

, FirstSortedValue(Date,-Date) as LastDate

, FirstSortedValue(Sal,-Date) as LastSal

, FirstSortedValue(Grade,-Date) as LastGrade

Resident Tab1

Group By Emp;

Re: Max date

Wed, 19 Dec 2012 23:32:54 GMT

appreciate your response but there are 50 different columns like salary..there are some complex conditions that are used for few graphs..I want to put this condition in the expression of the graph rather than in the script any help

Re: Max date

nagaiank — Wed, 19 Dec 2012 23:42:00 GMT

The FirstSortedValue function works in chart expression also.

So, if you create a pie chart with the following dimension and expression, the chart will plot using the latest salary of employees:

Dimension: Emp

Expression: Aggr(FirstSortedValue(Sal,-[Date]),Emp)

This works.

Re: Max date

sujeetsingh — Thu, 20 Dec 2012 04:28:24 GMT

Why not to use a canditional expression...

only({$<Dtae={$(min(interval(Today-Dta,d)))}>}

Re: Max date

Thu, 20 Dec 2012 19:55:43 GMT

Hi krishnamoorthy,

Appreciate your help.

what would be the expression if I need percentage of latest Grades for employee..

currently Ihave Grade in dimension and expression I have count(emp)/count(total emp)...now I need to pick only latest grades in the dimension instead of all grades..

please help..

Re: Max date

Thu, 20 Dec 2012 19:56:20 GMT

hi this did not work..there was no change in data..

thanks for the help...

Re: Max date

rwunderlich — Thu, 20 Dec 2012 21:16:06 GMT

You don't need to do division to get the relative percentage, use the "Relative" expression property instead.

Dim: Emp

Expression: FirstSortedValue(Sal, -Date)

Check the "Relative" checkbox on the expression.

-Rob

Re: Max date

Thu, 20 Dec 2012 21:43:36 GMT

Hi Rob,

I need the count of employees based on latest grade..each employee can have multiple grades I need to consider only grades based on recent dates..

thanks for the help...

Re: Max date

rwunderlich — Fri, 21 Dec 2012 01:30:04 GMT

"I need to consider only grades based on recent dates.".

That's what the ForstSortedValue() does. It includes only Sal from the latest Date for each employee. Isn't that what you are trying to do?

FirstSortedValue(Sal, -Date)

Re: Max date

rlp — Fri, 04 Jan 2013 09:45:28 GMT

Use the following code:

max_table:

NoConcatenate

LOAD

Emp ,

Max( Date ) as Date

RESIDENT <your_table>

GROUP BY Emp

;

LEFT JOIN( max_table )

LOAD

RESIDENT <your_table> ;

DROP TABLE <your_table> ;