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

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

rp2018 — Fri, 15 Jun 2018 16:53:36 GMT

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

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

aashish_21nov — Sat, 16 Jun 2018 07:36:04 GMT

Make the inner join rejects true.

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

rp2018 — Sun, 17 Jun 2018 15:13:40 GMT

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

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

rp2018 — Sun, 17 Jun 2018 15:22:36 GMT

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

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

aashish_21nov — Sun, 17 Jun 2018 16:06:09 GMT

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

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

rp2018 — Mon, 18 Jun 2018 20:26:50 GMT

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?

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

rp2018 — Mon, 18 Jun 2018 21:39:39 GMT

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