topic Re: Group by Clause - Query in QlikView

Group by Clause - Query

Mon, 01 Jun 2015 09:42:15 GMT

Hi ,

I have 2 QVDs . One QVD has employee details for 36 months so say if headcount is 100 this qvd will have 3600 rows. That means I am creating a snapshot for every month. I have another qvd which has employee IDs who has raised cases. So I need to pick out the latest record from first QVD and join it with the second QVD.

Now problem is I need to write a group by clause to find out max record for every employee and join them but when doing that I am getting duplicates reason is there are some possibilities that one employee may have worked in 2 depts and qlikview is forcing me to include dept also in group by clause.

Date Glimpse:

Master:

Date Name Dept Division

42133 Dee 1 Div1

42178 Dee 2 Div1

42183 Dee 3 Div2

So Query I wrote is(Since Qlikview forcing to include all other fields in group by) :

Load Max (Date) as Latest ,Name,Dept,Division

from Master

group by Name,Dept,Division;

Result:

42133 Dee 1 Div1

42178 Dee 2 Div1

42183 Dee 3 Div2

But what I wanted to achieve is :

42183 Dee 3 Div2 hic‌ JohnW‌ jaganmohan‌ ashfaq_haseeb‌ jontydkpi‌ afd‌

I cannot take latest month alone reason is I may have some employees who has already left the company by this month. So I had to take all the 3 years empl db and find the last entry for that employee and join it with the another table.

Your help is highly appreciated.

Re: Group by Clause - Query

MayilVahanan — Mon, 01 Jun 2015 09:48:21 GMT

Try like this

Load Max (Date) as LatestDate ,Name

from Master

group by Name;

Then join with the second table.

Edit:

If you need all other information means, inner join with original table (with key combination of Name and Date) and join with the second table

Re: Group by Clause - Query

Gysbert_Wassenaar — Mon, 01 Jun 2015 09:49:32 GMT

Try something like this:

Load

Max (Date) as LatestDate ,

Name,

FirstSortedValue(Dept, -Date) as LatestDept,

FirstSortedValue(Division,-Date) as LatestDivision

from Master

group by Name;

Re: Group by Clause - Query

swuehl — Mon, 01 Jun 2015 09:52:51 GMT

Try FirstSortedValue:

Load Max (Date) as Latest ,Name, FirstSortedValue(Dept, -Date) as Dept, FirstSortedValue(Division,-Date) as Division

from Master

group by Name;

Re: Group by Clause - Query

Mon, 01 Jun 2015 10:29:21 GMT

Thanks all so I believe Firstsortedvalue will get me latest value based on max(Date) right???

Re: Group by Clause - Query

swuehl — Mon, 01 Jun 2015 10:32:21 GMT

It will give you the first value sorted by -Date (negating the Date will sort descending, latest first).