Four Stars

How to compare <> in tmap between source and lookup?

I have a source and several lookups and am using tmap to do the comparison. 

 

How do you do <> comparison between source and lookups?

Source = orderdetail

lookup = itemdesc

 

This is an example of a query:

select count(*) from orderdetail a,

itemdesc b

where a.itemnbr <> b.itemnbr

6 REPLIES
Six Stars

Re: How to compare <> in tmap between source and lookup?

one.png

 

 

 

Make the inner join rejects true.

Regards
Aashish
Four Stars

Re: How to compare <> in tmap between source and lookup?

If I have 1 source and 3 lookups and need to grab few fields from lookup1, lookup2,lookup3 and source.  Do you have an example you can show me?  This will be <> comparison between all. 

 

Thanks

Four Stars

Re: How to compare <> in tmap between source and lookup?

Ashish,

 

I'm really new with Talend and trying to figure out how we can do this in tmap.

Have source and 2 lookups

source.id = lookup1.id and lookup2.Lastupdateddate is Null and
(source.field1<>lookup2.field1 or source.field2<>lookup2.field2)
if criteria matches, then expire lookup2 record set lastupddateddate = currentdatetime.
if record doesn't exist then insert a new record.  

 

Thanks

Six Stars

Re: How to compare <> in tmap between source and lookup?

if possible, share all source file with me and I will let you know how to get your expected result, 

Regards
Aashish
Four Stars

Re: How to compare <> in tmap between source and lookup?

When a new id is added in the source table, it doesn't insert. 

 

1.  Ran the job with no records in the lookup table.

2.  It inserted new records in the lookup table

3.  Added another id in source which doesn't exist in the lookup table.

4.  Reran the job and it doesn't insert the new id.

What should I change in tmap?

 

Four Stars

Re: How to compare <> in tmap between source and lookup?

Aashish,

 

My source is coming out of sql db.  I've created dummy tables and records.

create table testsource
(id int,
field1 nvarchar(20),
field2 nvarchar(50));

insert into testsource
values(1,'item1','1 chair'),
(2,'item2','5 sofas')

create table testlookup1
(id int,
lkid int)

insert into testlookup1
values(1,100)
(2,101)


create table testlookup2
(lkid int,
field1 nvarchar(20),
field2 nvarchar(50),
lastupdateddate datetime)

insert into testlookup2
values(100,'item1','1 chair','2018-06-17 13:37:08.347')
(100,'item1','2 chairs','')
(101,'item2','1 sofa','2018-06-17 13:37:08.347')
(101,'item2','2 sofas','2018-06-18 08:31:00.000')
(101,'item2','4 sofas','')

 

Thanks