Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

juraj_misina
Luminary Alumni
Luminary Alumni

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