topic Re: How to check that multiple, specfic records in multiple columns have data in QlikView

How to check that multiple, specfic records in multiple columns have data

Tue, 20 Sep 2016 19:38:36 GMT

So, I am working on an app that will deal with, in part, employee satisfaction of certain employee requirements. I want to create a field in the data (in the QDV) that would indicate if the user has met the requirements. From there, I will use a distinct count to get the number of employees in a dept/division/region/etc that do not meet requirements. for instance, a piece of my table would look like this:

EMPLOYEE	POSITIONCERT	USERCERT
Jim Bob Cooter	Driver's Licence	Driver's Licence
Jim Bob Cooter	Canuter Valve Repair	Canuter Valve Repair
Jon Crompton	Driver's Licence	Driver's Licence
Jon Crompton	Safety Certification	Null
Jon Crompton	Sensitivity Training	Sensitivity Training
Casey Clausen	Driver's Licence	Null
Casey Clausen	High School Degree	High School Degree
Casey Clausen	Sexual discrimination Certification	Null

In this example, POSITIONCERT details what is associated with the position of the employee, USERCERT details what the employee actually has. I want to have a field that details Jim Bob as having all of his requirements and Jon and Casey have not met all of their's. I can't really think of a way to tell Qlikview to look at each line associated with each employee and if one of the multiple lines does not have a requirement met, to indicate that in a field. Does anybody have any ideas as to where I could start?

Thanks for your time!

Re: How to check that multiple, specfic records in multiple columns have data

neelamsaroha157 — Tue, 20 Sep 2016 20:24:46 GMT

You can create a flag in script as If(POSITIONCERT = USERCERT ,1,0) as Flag

and then in next step you can Count the Number of POSITIONCERT aggregated by Employee and Compare it with sum of Flag.

For ex. Number of POSITIONCERT for Jon Crompton =3 but sum of Flag would be 2 so he doesn't meet the requirements.

Hope this helps..

Re: How to check that multiple, specfic records in multiple columns have data

MarcoWedel — Tue, 20 Sep 2016 21:34:34 GMT

Hi,

one solution might be:

table1:
LOAD * FROM [https://community.qlik.com/thread/233479] (html, codepage is 1252, embedded labels, table is @1);

table2:
LOAD EMPLOYEE,
    Max(POSITIONCERT=USERCERT) as AllRequMet
Resident table1
Group By EMPLOYEE;

hope this helps

regards

Marco

Re: How to check that multiple, specfic records in multiple columns have data

Wed, 21 Sep 2016 15:19:12 GMT

This is the way that I decided to do it. I simply didn't think about using the MAX() function like that, but it's a good one to have up my sleeve now. Thanks for the advice!