One Star

Separating unique matches and multiple matches in inner join

Hello everyone,
I have a problem using tMap component to perform an inner join with output filter. My project is in Java in TOS 2.4.1.
I have to Oracle Inputs :
- my main table contains a list of events, each one with a given date and attached to a customer :
EVENT_ID | CUSTOMER_ID | EVENT_DATE
- my lookup table contains a list of periods, each one with a begin date, an end date, and attached to a customer :
PERIOD_ID | PERIOD_BEGINDATE | PERIOD_ENDDATE | CUSTOMER_ID | PERIOD_TYPE
For each event, based on the same customer, I want to know which period corresponds by comparing PERIOD_BEGINDATE < EVENT_DATE < PERIOD_ENDDATE
I used a tMap with an inner join (all matches) on the CUSTOMER_ID and an output filter on the dates (see Image 1 & 2)
My problem is that some events matches several periods... I would like to separate Events with one period matching and Events with several period matchings, in order to apply a second filter on the latter (based on the period_type).
I can't find the way to perform this separation...
I tried to replicate the output and perform an AgreggateRow to count the rows by event and filter on the ones who have COUNT > 1. Then I tried to "inner join" the list of events with COUNT > 1 with the initial output.... but the second tMap doesn't accept both as inputs (see Image 3).
Thanks a lot for your help.
9 REPLIES
One Star

Re: Separating unique matches and multiple matches in inner join

Any clue? I'm still stuck with that problem...
Thank you
One Star

Re: Separating unique matches and multiple matches in inner join

Hi Vargas,
just a idea:
First a flow in TOS is always straight forward, you are not able to link any component so that there is a doubled path.
a) In your first tMap add a id in your output with Numeric.Sequence() and use your key (CUSTOMER_ID) as key for the sequence.
b) Sort your output on your business key and the sequence (last one descending).
c) Aggregate the flow on all attributes except the sequence. For the sequence use max.
d) Now you can filter your flow in a stream with sequence == 1 and sequence != 1.
e) For all sequence != 1 apply a specific filter.
Bye
Volker
One Star

Re: Separating unique matches and multiple matches in inner join

Hi,
Thank you for your response.
I tried your solution, with the sequence based on the EVENT_ID (because a CUSTOMER can be associated to several events).
In the output of the tMap I get this :
EVENT_ID | CUSTOMER_ID | ... | PERIOD_ID | ... | PERIOD_TYPE | SEQ_ID
00000001 | 0800001 | .... | P00001 | ... | A | 1
00000002 | 0800002 | .... | P00002 | ... | B | 1
00000003 | 0800003 | .... | P00003 | ... | C | 1
00000003 | 0800003 | .... | P00004 | ... | D | 2
But the sort + associate doesn't change anything because each eventID+periodID is different.
Of course when I apply the filter I get :
MAX(SEQ_ID) = 1 :
EVENT_ID | CUSTOMER_ID | ... | PERIOD_ID | ... | PERIOD_TYPE | SEQ_ID
00000001 | 0800001 | .... | P00001 | ... | A | 1
00000002 | 0800002 | .... | P00002 | ... | B | 1
00000003 | 0800003 | .... | P00003 | ... | C | 1

And MAX(SEQ_ID) != 1 :
EVENT_ID | CUSTOMER_ID | ... | PERIOD_ID | ... | PERIOD_TYPE | SEQ_ID
00000003 | 0800003 | .... | P00004 | ... | D | 2

Instead of two groups :
Unique matches :
EVENT_ID | CUSTOMER_ID | ... | PERIOD_ID | ... | PERIOD_TYPE | SEQ_ID
00000001 | 0800001 | .... | P00001 | ... | A | 1
00000002 | 0800002 | .... | P00002 | ... | B | 1
Multiple matches :
EVENT_ID | CUSTOMER_ID | ... | PERIOD_ID | ... | PERIOD_TYPE | SEQ_ID
00000003 | 0800003 | .... | P00003 | ... | C | 1
00000003 | 0800003 | .... | P00004 | ... | D | 2

Meanwhile I'm going to look for another way to use the Numeric.sequence() function, this might lead to the solution..
One Star

Re: Separating unique matches and multiple matches in inner join

I have been torturing my brain for two days now and still no solution...
I tried to use global vars too, but I don't manage to filter with them correctly.
Anyone coming up with an idea?
Thanks a lot.
One Star

Re: Separating unique matches and multiple matches in inner join

I have come up with the same kind of problem.
I found a solution by duplicating the lookup table.
Use twice the same lookup table, make an inner join with a unique match between the main row and the first lookup table. Create an outer join with "all matches" between the two lookup tables, on the same key. In the filter expression of the second lookup table, use a condition like : row3.key != row2.key (in correct java it would be !row3.key.equals(row2.key) ).
Then create two outputs "inner join reject". In the first one, use the filter expression : row3.key == null; and in the second one use the expression row3.key != null.
Hope this will work for you, you might have to change one or two details but I think you should consider this solution of duplicating the lookup table.
One Star

Re: Separating unique matches and multiple matches in inner join

Hi Chris,
yes I'm now understanding that you need additional values and you cant use tAggregateRow. So I would say you should do it in two steps:
First remember which values have more than one output (based on my solution with the sequence)
And the reprocess the data with an additional lookup.
Or did the solution of mkxx2 solved your problem?
Bye
Volker
One Star

Re: Separating unique matches and multiple matches in inner join

Hi everyone, thank you for your answers.
I tried both solutions and they work ! At first I had the idea to duplicate lookups but I was afraid that it might be too hard for my database to perform the same query twice. But I solved it by putting my DB query in a temporary file. Then I use this temp file as an input to reprocess my data.
That works real fine!
Thank you all.
One Star

Re: Separating unique matches and multiple matches in inner join

If I understand well, in the 2 solutions, you have to duplicate the lookup row component (or put the result in a file)
Did someone find another solution without lookup row component duplication ?
One Star

Re: Separating unique matches and multiple matches in inner join

Find the files you are looking for at pdf-explorer.com the most comprehensive source for free-to-try files downloads on the Web