Group by Clause - Query

[#1]

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.

[MayilVahanan]

Hi

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

[Gysbert_Wassenaar]

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;

[swuehl]

Try FirstSortedValue:

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

from Master

group by Name;

[#5]

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

[swuehl]

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