Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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
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 ?)..
Hi Michele,
Thank you for reply, can u pls have a look at my qvw and let me know what i should change.
Ok now tell me where we need to look and which output is not coming correct?
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
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;
Hi Mohammed,
Keys are ok.
The only problem is that not all users of user table (GEBRUIKER) are defined on UserRights table (GEBRUIKERRECHT).
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?