topic Re: Compare Active Directory Groups in QlikView

Compare Active Directory Groups

Mon, 21 Dec 2015 13:22:26 GMT

Hi all.

I'm working with CSV files exported from our AD and HR system. I'm willing to compare Active Directory Groups between 2 users and show in a chart (Straight Table) only the groups User1 has (so hide groups found for User1 and User2).

I tried the simple way but cannot make it work... Then I tried to work with Alternate States, but still no luck...

I've already worked with QlikView in the past but at the moment I'm a bit rusty

What would be the best way to compare the groups of 2 users?

Best regards.

Re: Compare Active Directory Groups

simospa — Mon, 21 Dec 2015 14:06:04 GMT

Hi,

try to post a simple sample (mask data if you don't want to show them).

Re: Compare Active Directory Groups

gandalfgray — Mon, 21 Dec 2015 14:19:32 GMT

A solution is to:

select User1
select groups excluded (now all groups which User1 have will be excluded)
select User2 (now you have the result you want. groups User2 but not User1 has)

Re: Compare Active Directory Groups

Mon, 21 Dec 2015 14:27:01 GMT

Here is an example:

Orignal table

===========

Username      Technical information

User1            ADGroup_Access-ToDiretory_PROD

User1           _ThisIsOldSchoolNaming

User1           ADGroup_Access-ToDiretory12_DEV

User1           ADGroup_Access-ToDiretory222_PROD

User1           ADGroup_Access-Software1_TESTING

User1           ADGroup_Access-ToDiretory333_PROD

User1           ADGroup_Access-Software133_TESTING

User2           ADGroup_Access-Software131_TESTING

User2           ADGroup_Access-Software45_TESTING

User2           ADGroup_Access-Software113_TESTING

User2           _ThisIsOldSchoolNamingAgain

User2           _ThisIsOldSchoolNaming

User2           ADGroup_Access-ToDiretory333_PROD

Result table

============

Username    Technical information

User1          ADGroup_Access-ToDiretory_PROD

User1          ADGroup_Access-ToDiretory12_DEV

User1          ADGroup_Access-ToDiretory222_PROD

User1          ADGroup_Access-Software1_TESTING

User1          ADGroup_Access-Software133_TESTING

Re: Compare Active Directory Groups

Peter_Cammaert — Mon, 21 Dec 2015 14:40:30 GMT

Or without too many manipulations except selecting users from a list:

Load a table with individual group membership (Group, User). The same group will have as many records as members.
Create a listbox for field users.
Create a straight table with dimension Group and expression =IF (count(User)<=1, 1) (no multiple members)
Create a straight table with dimension Group and expression =IF (count(User)>1, Count(User)) or =IF (count(User)>1, Concat(User, ', ')) (at least two members)

The first straight table will show for every selection in listbox User the groups that have no two members from the selection. The second straight table will show all groups that have at least two members from the selection.

See attachment

Best,

Peter

Re: Compare Active Directory Groups

lironbaram — Mon, 21 Dec 2015 14:47:17 GMT

another approach is to create an alternate state (G1)

now assuming you select one user in the regular state

than you select reference user in the alternate state

your expression will be

=Concat({<F2=P(F2)-p({[G1]}F2)>}F2,',')

where F2 is your group field

Re: Compare Active Directory Groups

lironbaram — Mon, 21 Dec 2015 19:35:04 GMT

have a look at the attach example

Re: Compare Active Directory Groups

Wed, 23 Dec 2015 08:39:53 GMT

Perfect, thanks a lot, that's exactly what I was missing. I used the expression you gave me in a Straight Table Chart. The comparison of AD groups between the "master" username and the other username works like a charm.

Re: Compare Active Directory Groups

lironbaram — Wed, 23 Dec 2015 19:14:19 GMT

great

it always nice to hear that you helped someone