Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have two flat file like,
I want only a mismatch value.How to get it.
See this sample
Hi sujeetsingh,
i can't able to load this file.Please give Query.
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;
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;
it seems that RamKumar was able to open the file and he has provided my script what i am using to reload.
Thanks RamKumar
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
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;
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.
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;