Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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 |
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?
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:
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:
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