How to join two tables

[#1]

Hi All,

         I have two flat file like,

                

I want only a mismatch value.How to get it.

[sujeetsingh]

See this sample

[#3]

Hi sujeetsingh,

                     i can't able to load this file.Please give Query.

[#4]

Pasting Sujit's code.

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='R # ##0,00;R-# ##0,00';

SET TimeFormat='hh:mm:ss TT';

SET DateFormat='YYYY/MM/DD';

SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table1:

LOAD * INLINE [

    EMP_ID

    1

    2

    3

    4

    5

];

table2:

LOAD * INLINE [

    EMP_ID1

    2

    3

    6

    8

    9

];

Join(Table1)

LOAD

EMP_ID1 as EMP_ID,EMP_ID1

Resident table2;

DROP Table table2;

DATA:

LOAD

EMP_ID,EMP_ID1,

if(EMP_ID1<>null(),'Match','MisMatch') as MisMatch

Resident Table1;

DROP Table Table1;

[sujeetsingh]

Table1:

LOAD * INLINE [

    EMP_ID

    1

    2

    3

    4

    5

];

table2:

LOAD * INLINE [

    EMP_ID1

    2

    3

    6

    8

    9

];

Join(Table1)

LOAD

EMP_ID1 as EMP_ID,EMP_ID1

Resident table2;

DROP Table table2;

DATA:

LOAD

EMP_ID,EMP_ID1,

if(EMP_ID1<>null(),'Match','MisMatch') as MisMatch

Resident Table1;

DROP Table Table1;

[sujeetsingh]

it seems that RamKumar was able to open the file and he has provided my script what i am using to reload.

Thanks RamKumar

[IAMDV]

Hi Mani,

One more of doing this without using the Join Statement.

Table1:

LOAD * INLINE [

    EMP_ID

    1

    2

    3

    4

    5

];

table2:

LOAD * INLINE [

    EMP_ID1

    2

    3

    6

    8

    9

];

Temp:

LOAD  EMP_ID AS My_Emp_ID, 'Table1' AS TableName Resident Table1;

Concatenate

LOAD EMP_ID1 AS My_Emp_ID,  'Table2' AS TableName Resident table2;

Final:

LOAD *

Where MyCount = 1;

LOAD Count(My_Emp_ID) AS MyCount, My_Emp_ID

Resident Temp

Group by My_Emp_ID;

Drop Table Temp;

Thanks,

DV

[#8]

hi

simply try this

suppose you have two tables.

Table1:

load

RowNo() as key,

Emp_Id

from Table1;

join

Table2:

load

RowNo() as key,

Emp_Id  as Emp_Id_new

from Table2;

OutputTable1:

load

Emp_Id as Emp_Id_output

resident Table1

where  Emp_Id <> Emp_Id_new;

OutputTable2:

load

Emp_Id_new as Emp_Id_output

resident Table1

where  Emp_Id <> Emp_Id_new;

[#9]

Hi,

Qualify *;

unqualify 'EmpID_key';

Table1:

load Emp_ID as EmpId_key,* ;

inner join

Table2:

load Emp_ID as EmpId_key, * ;

Its show only those records which matched based on EmpId_key, remember after that join table1 and table2 colums are in same column line, also rename other column name of table2 which is same name in table1 (or use qualify and unqualify), other wise QV join other columns as a composit key (Syntathic key) which is same name in both tables.

Regards

Zain.

[er_mohit]

Try this

Table1:

LOAD   rowno() as Key, * INLINE [

    EmpID1

    1

    2

    3

    4

    5

];

Join

Table2:

LOAD  rowno() as Key,* INLINE [

    EmpID2

    1

    2

    6

    7

    8

];

New:

LOAD if(EmpID2<>EmpID1,EmpID2) as EmpID

Resident Table1;

join

LOAD if(EmpID2<>EmpID1,EmpID1) as EmpID

Resident Table1;

DROP Table Table1;