Issue with getting latest record from multiple records for an employee

[#1]

Hi,

I am having a problem with getting the latest record from multiple records.

I have attached the sample , please let me know if you have any idea on implementing this requirement.

The details of the req are mentioned in the attachment.

Thanks,

Srihari

[disqr_rm]

Have a look here: http://community.qlik.com/forums/t/32828.aspx

What you need to use is FirstSortedValue() function.

Rakesh

[#3]

Hi Rakesh,

I have treid the expression. Its not working for me.

=FirstSortedValue(filenumber,- TransOutYearMonth,1)

Can we use two feilds instead of one in the above expression. Ie., i need to get the max value in TransOutYearMonth and TransInYearMonth.

Some thing like this :

=FirstSortedValue(filenumber,- {TransOutYearMonth,TransInYearMonth},1)

Please suggest.

Thanks,

Srihari

[#4]

I'm not 100% sure what you are looking for, but if you want the max date that is in either the TransOutYearMonth or the TransInYearMonth fields you might want to consider changing the data model. What I mean is to combine the TransOutYearMonthfield and the TransInYearMonth into one field and add another field called like [TransDirection] or something (I don't really know what the fields mean but you get the idea) and it can be 'In' or 'Out'. You could probably do it the way it is now but... I don't really know how unless the solution below is what you want.

Alternatively, maybe this will work with EMPLID, TransInYearMonth and TransOutYearMonth as dimensions. I don't know if this is what you want either but it looks to me like the TransInYearMonth field is always the same for each EMPLID so this might be fine.

=if([TransInYearMonth]=max(total <[EMPLID]> [TransInYearMonth]) AND [TransOutYearMonth]=max(total <[EMPLID]> [TransOutYearMonth]),[TransOutActReason])

[#5]

It can't be, there can be cases where in TransferInDate can be greater than TransferOutDate. In that case the condition should return the TransferInDate.

First of all 1 question, Even if i can get the above condition right, what is the feasibilty that i can apply the set analysis on the records returned from the above query.

My idea is to get the most recent record for each employee. And then i need to apply my set analysis on the records returned on the selection.

Please let me know if this is feasible or l must go for a design change.

Gud to hear from a knowledgable person and thanks for sharing.

Thanks,

Srihari

[#6]

Hi,

One more thing i would like to ask you, if you go through the expression i have written i am telling it to remove the records if the transferInDate is Less than the transfer out date., see for the emploee 001, for the second record the transferInDate is less than the TransferOutDate. Still it is not getting eliminated. Except these records , the expression works perfect for other cases.

I have seen cases where the expression is removing the records with TransferInDate less than TransferOutDate.

Does this behaviour due to having the same TransferInDate for both the records ?

Why is this weird behaviour?

Can you share any document / some thing on how QlikView do the Set analysis. IS it record by record ? .. If so i should get this record eliminated if I am not wrong

[#7]

I'm having trouble understanding some of your questions but I think if you combine the TransferInDate field and the TransferOutDate field into one field and make another field to indicate whether it is an in date or an out date that things might work out better. I'm not sure... but it's something to think about.

Set analysis is calculated once per chart, not once per row (or per record). That being said, for your example you are not going to be able to use set analysis to compare the max TransInDate or the max TransOutDate or anything like that.

The set analysis you have in your expression is so complicated that I would pretty much bet there is something wrong with it. That's not to say it's wrong but... I'd almost guarantee it's overly complicated.

Here's something else you could try. Only put EMPLID as dimension:

=if(max([TransInYearMonth])<max([TransOutYearMonth]),max([TransOutYearMonth]),
 if(max([TransInYearMonth])>=max([TransOutYearMonth]),max([TransInYearMonth])))

Again, I think your best bet is to combine the dates into one field and add another field to indicate whether it is an In date or an Out date. I'm having a tough time figuring out exactly what you want but hopefully something there helped... good luck