topic Re: Finding the duplicated and changed values in a table in QlikView

Finding the duplicated and changed values in a table

Tue, 10 Nov 2015 04:13:50 GMT

I have a table like which shows the staff name, the unit they belong to and their login time to a system.

User Unit LoginTime

Jane Customer Service 12.00

Jane Customer Service 13.00

Jane Customer Service 14.00

Peter Sales & Marketing 22:00

Peter Sales & Marketing 22:34

Peter Sales & Marketing 07:00

David Executive Office 14:00

David Executive Office 15:02

David Sales & Marketing 16:07

Now I want to create a table which display users that have moved units.

So I will have something like this

User Unit LoginTime

David Executive Office 15:02

David Sales & Marketing 16:07

I am new to qlikview so any help would be much appreciated!!!

Re: Finding the duplicated and changed values in a table

Gysbert_Wassenaar — Wed, 11 Nov 2015 13:21:24 GMT

See attached example.

Re: Finding the duplicated and changed values in a table

Thu, 12 Nov 2015 02:11:41 GMT

Thank you so much for your help. Really do appreciate it!

As I am very new to qlikview, I am wondering if you could briefly explain this

=max({<AuthenticatedUser={"=count(distinct [AD User department])>1"}>}[Start DateTime])

I am struggling to understand what the count(distinct [AD User department])>1 would produce, is it just a number?

Also is it possible to get rid of the circle part below?

Re: Finding the duplicated and changed values in a table

Gysbert_Wassenaar — Thu, 12 Nov 2015 06:34:53 GMT

This is a filter: {<AuthenticatedUser={"=count(distinct [AD User department])>1"}>}. It will select those AuthenticatedUser values that have more than one Unit. That's what the count distinct is for.

You can set the Total mode of the expression to No Totals to disable the total of the expression.