Flags count

[#1]

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...

[alexandros17]

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

[#3]

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;

[#4]

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

[#5]

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.

[#6]

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...

;

[maxgro · Accepted solution]

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;