Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kiranjay
New Contributor III

Compare selections from one field value with another field value

Hi All,

I am building an app to check the data before loading into  qlik layer.

The scenario is to find the fields which is associated with multiple values in other table. (i.e) 1 employee should be associated only to 1 manager in the same month.

If 1 employee is associated to different managers in the same month then it is data error.

Manager initialManager NameEmployee InitialEmployee NameMonth
JPJohnSASamsungJan - 18
KSKrishNSNathan SamFeb - 18
PLPrashanthKRKarthikFeb - 18
RNRizwanKRKarthikFeb - 18
kSKishoreLALaxmanFeb - 18

In the given example excel if 1 "employee initial & Employee Name" combination is associated to 2 different "manager initial & manager Name"  combination in the same month, then it is error in data.

Here, Employee 'Karthik' is linked to 2 different managers 'Prashanth' and 'Rizwan' in the same month. which needs to throw error in the application which I am building.

Could anyone help me out in this?

2 Replies
OmarBenSalem
Esteemed Contributor

Re: Compare selections from one field value with another field value

Maybe:

table:

load *, "Employee Name"&'|'&Month as Key Inline [

Manager initial, Manager Name, Employee Initial, Employee Name, Month

JP, John, SA, Samsung, Jan - 18

KS, Krish, NS, Nathan Sam, Feb - 18

PL, Prashanth, KR, Karthik, Feb - 18

RN, Rizwan, KR, Karthik, Feb - 18

kS, Kishore, LA, Laxman, Feb - 18

];

NoConcatenate

Final:

load Key, if(CountManager=1,SubField(Key,'|',1),'Error for Employee '&SubField(Key,'|',1)) as "Employee Name",SubField(Key,'|',2) as Month ;

load  Key, count( DISTINCT "Manager initial") as CountManager Resident table

group by Key

;

Left join(Final)

load Key, "Manager initial", "Manager Name", "Employee Initial" Resident table;

Drop Table table;

drop field Key from Final;



Result:

Capture.PNG

If u just want to import the Employees that have one manager per Month:

table:

load *, "Employee Name"&'|'&Month as Key Inline [

Manager initial, Manager Name, Employee Initial, Employee Name, Month

JP, John, SA, Samsung, Jan - 18

KS, Krish, NS, Nathan Sam, Feb - 18

PL, Prashanth, KR, Karthik, Feb - 18

RN, Rizwan, KR, Karthik, Feb - 18

kS, Kishore, LA, Laxman, Feb - 18

];

NoConcatenate

Final:

load Key, SubField(Key,'|',1) as "Employee Name",SubField(Key,'|',2) as Month  where CountManager=1;

load  Key, count( DISTINCT "Manager initial") as CountManager Resident table

group by Key

;

Left join(Final)

load Key, "Manager initial", "Manager Name", "Employee Initial" Resident table;

Drop Table table;

drop field Key from Final;


Result:

Capture.PNG

Luminary
Luminary

Re: Compare selections from one field value with another field value

Hi Kiran,

you can do so in script:

LOAD

     [Employee Initial],

     [Employee Name],

     Month,

     If(Count([Manager Initial]&[Manager Name])>1, 'Error', 'OK') as ErrorCheck

From Source

Group By

     [Employee Initial],

     [Employee Name],

     Month

;

And analogically for other required checks.

Hope this helps.

Juraj

Community Browser