Joins

[#1]

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.

[its_anandrjs]

Why you use Employee table because you have updated info in the Service table let me know.

[alexandros17 · Accepted solution]

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

[tresesco]

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;

[Anonymous]

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.

[#6]

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

[#7]

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

[alexandros17]

Always remember to write noconcatenate

FinalEmpList:

noconcatenate

LOAD *

resident MyNewTable

where myFlag=1;

drop table MyNewTable;

Let me know