Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have two tables 'Table1' with 20 records and 'Table2' with 8 records.
When I left join Table2 to Table1 how do I flag those records in Table1 that DOESNOT match. From the example above I need to flag 12 records. From the below script I can count the flag to get 8 records.
Please see the script I have used below.
Table1:
Load
unique id,
Name
from...
Left join (Table1)
Load
unique id,
type
1 as flag
from...
t1: load rowno() as unique_id, 't1 name ' & rowno() as name AutoGenerate 20;
t2: load rowno() as unique_id, 't2 type ' & rowno() as type AutoGenerate 8;
Map:
Mapping load unique_id, 0 Resident t2;
Right join (t2)
load
*,
applymap('Map', unique_id, 1) as flagnotmatch
Resident t1;
DROP Table t1;
You cannot because left join from Tab2 on Tab1 "Exclude" records not existing in Tab2 (Tab2 is the starting set of data)
To achive the result you need (the easiest way but there are several ways)
1) join the tables so you have all the combinations among records
2) load again the resulting table in order to flag what you need (something like: if(isnull(fieldfromTab1),'1','0') as flag)
Let me know
In this scenario, Left join is not give what do you need.
First do the Join and create the Flag on resident load like below:
Temp:
LOAD
UniqueKey,
UniqueKey AS Flag,
Name
From Table1;
Join (Temp)
LOAD
UniqueKey,
Type
From Table2;
Final_Table:
Noconcatenate
LOAD
UniqueKey,
IF(isNull(Flag) ,1 ,0) AS Flag,
Name.
Type
Resident Temp;
DROP Table Temp;
True. But the problem is I have more thn 3 tables and to take the resident after the joins is painful as the tables have millions of records and I get an error saying 'Exceeding allocated memory 2MB' etc...
Table1:
Load
Unique id
field1.1
field1.2
Left join (Table1)
Unique id
field2.1
field2.2
concatenate Table1
Load
Unique id2
field3.1
field3.2
Left join (Table1)
Unique id2
field4.1
field4.2
It doesn't matter how many tables you have. As per your script don't do concatenate (concatenate Table1) at first place. Create the temp tables first and finally concatenate the tables.
Please post full table details so community will help more.
Try
mapT2a:
Mapping load
unique id,
type
from ....
Table1:
Load
unique id,
Name,
if (tmpType<>0,tmpType) as Type,
if (tmpType=0,0,1) as flag
;
Load
unique id,
Name,
applymap('mapT2a',unique id,0) as tmptype
from...
;
t1: load rowno() as unique_id, 't1 name ' & rowno() as name AutoGenerate 20;
t2: load rowno() as unique_id, 't2 type ' & rowno() as type AutoGenerate 8;
Map:
Mapping load unique_id, 0 Resident t2;
Right join (t2)
load
*,
applymap('Map', unique_id, 1) as flagnotmatch
Resident t1;
DROP Table t1;