topic Beginner Qlikview in QlikView

Beginner Qlikview

Wed, 25 Nov 2020 16:16:04 GMT

Goodmorning,

Can someone please help me out. Am trying to make a dashboard based on the difference between user rights and group rights in an application. I have a user table [GEBRUIKER], UserRights table [GEBRUIKERRECHT], SystemRight table [VDHRECHT], Usergroup rights table[GEBRUIKERSGROEPRECHT] and finally the UserGroups[GEBRUIKERSGROEP].

If i try to see which users belong to which group, i dont seem to get the right result. What am i doing wrong? Thank you in advance for your help.

Mustapha.

Re: Beginner Qlikview

Anil_Babu_Samineni — Thu, 07 Sep 2017 09:49:33 GMT

If you show the image about Data model, That is ok to me until unless provide real information. Would you share QVS file so then we came to know what you made wrong and we will show you the right approach if needed

Re: Beginner Qlikview

micheledenardi — Thu, 07 Sep 2017 10:01:10 GMT

Hi Mohammed,

as you can imagine the problem is on the keys but without some sample data it's not possible to give a specific answer.

For example you can try to start from the user table and check the key between user table and user rights table (USERID_MAGIC), if the key is fine, check the next key (VDH_RECHTUNIEK) and then the final key (GEBRUIKERGROEP_UNIEK).

For all keys you have to check if the format is the same (upper or lower case), il the lenght is the same (are there leading zeros ?)..

Re: Beginner Qlikview

Thu, 07 Sep 2017 10:18:41 GMT

Hi Michele,

Thank you for reply, can u pls have a look at my qvw and let me know what i should change.

https://we.tl/Phi4WNEKNa

Re: Beginner Qlikview

Thu, 07 Sep 2017 10:18:51 GMT

https://we.tl/Phi4WNEKNa

Re: Beginner Qlikview

Anil_Babu_Samineni — Thu, 07 Sep 2017 10:22:16 GMT

Ok now tell me where we need to look and which output is not coming correct?

Re: Beginner Qlikview

Thu, 07 Sep 2017 10:25:35 GMT

when i make a table and add all the users [Gebruiker] en follow that up with the groups [gebruikersgroep] its doesnt connect properly.

For example i expect to see Danny Plug as the only person on Uren Doorboeken

Re: Beginner Qlikview

Anil_Babu_Samineni — Thu, 07 Sep 2017 10:34:59 GMT

May be try with 4th Table like JOIN or use Right Join (GEBRUIKERSGROEPRECHT)

GEBRUIKERGROEP:

LOAD

"GBG_GEBRUIKERGROEP_CODE" ,

"GBG_GEBRUIKERGROEP_OMS",

"GBG_WISMARKERING",

"GBG_UNIEK" AS GEBRUIKERGROEP_UNIEK;

SQL SELECT *

FROM HMS.GEBRUIKERSGROEP;

JOIN // OR Right Join (GEBRUIKERSGROEPRECHT)

GEBRUIKERSGROEPRECHT:

LOAD "GGR_GEBRUIKERGROEP_CODE" ,

"GGR_VDH_RECHTUNIEK" AS VDH_RECHTUNIEK,

"GGR_BEKIJKRECHT",

"GGR_TOEVOEGRECHT",

"GGR_WIJZIGRECHT",

"GGR_VERWIJDERRECHT",

"GGR_GEBRUIKERGROEP_UNIEK" AS GEBRUIKERGROEP_UNIEK;

SQL SELECT *

FROM HMS.GEBRUIKERSGROEPRECHT;

Re: Beginner Qlikview

micheledenardi — Thu, 07 Sep 2017 10:42:28 GMT

Hi Mohammed,

Keys are ok.

The only problem is that not all users of user table (GEBRUIKER) are defined on UserRights table (GEBRUIKERRECHT).

Re: Beginner Qlikview

Peter_Cammaert — Thu, 07 Sep 2017 10:46:14 GMT

Your datamodel is a bit weird, in that it will lead to filtering of users who drop out of groups because the rights do not match.

IMHO, you should connect your users table to your groups table to get the membership sorted out. Then you connect the UserRights table to the Users and the GroupRights table to the Groups. It doesn't matter that rows get duplicated, because QlikView doesn't need a normalised database.

In practice, I have a problem understanding what the SystemRights table is and how it fits in. What do these rights mean and when do they apply to whom? Are they being assigned to both user and groups?