Recent hired employees

[dusasuman]

Hi,

I have a data with employee information and sales amount. the table has fields like Emp, HireDate, Term, Amount.

Requirement:

I need to show the data in a straight table and i need to show records of employees who have been hired recently means the record has the latest date in it and when Term is equal to null or blank.

Here i am sending a sample data "sheet1", and i have shown the output in next sheet. we need to bring the same output in qlikview. It has to be done at UI. charts...

Please help me out.

[swuehl]

Maybe like this, create a straight table with the two expressions as stated below):

Emp	Max(if(Len(Trim(Term))=0,HireDate))	FirstSortedValue(Amount, if(Len(Trim(Term))=0,-HireDate))
		170
David	04.09.2014	15
James	10.02.2016	20
Joseph	03.01.2015	55
Roy	05.05.2016	80

[alexpanjhc]

Mine is not as smart as swuehl's solution but I think that it will be easier to create other charts

temp:

LOAD Emp,

      max(date(HireDate))  as HirDate,

    Term,

      Emp&max(date(HireDate))&Term as Key

FROM

(ooxml, embedded labels, table is Sheet1)

where isnull(Term)

group by Emp, Term

;

left join (temp)

LOAD Emp&date(HireDate)&Term as Key,

    Amount

FROM

(ooxml, embedded labels, table is Sheet1);