Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following requirement. I have two tables:
employee:
LOAD * INLINE [
name, code
"abc", 1
"cdf", 1
"py", 2
"xyz",2
];
service:
LOAD * inline
[
name,service,code
"abc","food",1
"abc","transport",2
"cdf","water",1
"cdf","food",2
"py","food",2
"xyz","food",2
];
Now , I want those sets of employee names from service table who have codes other than the one mentioned in employee table. For eg: abc(since it has code 2 instead of 1), cdf (since it has code 2 instead of 1).
Can anybody help me with the join statement required here.
Thanks in advance.
MyTable:
nococncatenate
Load name, code as code_Emp resident employee;
left join
Load name,service,code as code_Ser resident service;
drop tables employee, service;
Now jou have one table with the fields :
name, code_Emp, service, code_Ser
At this point:
MyNewTable:
noconcatenate
Load *.
if (code_Emp <> code_Ser,1,0) as myFlag
resident MyTable;
drop table MyTable;
Now if you test myFlag as 1 you will find what you need
Why you use Employee table because you have updated info in the Service table let me know.
MyTable:
nococncatenate
Load name, code as code_Emp resident employee;
left join
Load name,service,code as code_Ser resident service;
drop tables employee, service;
Now jou have one table with the fields :
name, code_Emp, service, code_Ser
At this point:
MyNewTable:
noconcatenate
Load *.
if (code_Emp <> code_Ser,1,0) as myFlag
resident MyTable;
drop table MyTable;
Now if you test myFlag as 1 you will find what you need
Try:
Employee:
LOAD name, code as code2 INLINE [
name, code
"abc", 1
"cdf", 1
"py", 2
"xyz",2
];
Left Join
service:
LOAD * inline
[
name,service,code
"abc","food",1
"abc","transport",2
"cdf","water",1
"cdf","food",2
"py","food",2
"xyz","food",2
];
NoConcatenate
Final:
Load *
Resident Employee where code<>code2;
Drop Table Employee;
or this:
employee:
mapping LOAD * INLINE [
name, code
"abc", 1
"cdf", 1
"py", 2
"xyz",2
];
service:
LOAD * inline
[
name,service,code
"abc","food",1
"abc","transport",2
"cdf","water",1
"cdf","food",2
"py","food",2
"xyz","food",2
];
new_service:
LOAD *,
if(ApplyMap('employee', name)=code,1,0) as flag
Resident service;
drop Table service;
now based on flag you can select the ones that have mismatching codes.
Hi Anand,
That is project requirement. I have just made sample inline tables.
Employee table contains only one record for each employee with a code.
Service table contains data for each employee ,each service. Code is incorrect in some data in this table.
I now need to figure out those employees for which codes are there which are diff than base code.
Regards
Thanks this works.
But why is when I am trying the following code it doesn't work:
FinalEmpList:
LOAD *
resident MyNewTable
where myFlag=1;
Thanks in advance
Always remember to write noconcatenate
FinalEmpList:
noconcatenate
LOAD *
resident MyNewTable
where myFlag=1;
drop table MyNewTable;
Let me know