lookup using pattern matching

One Star

lookup using pattern matching

Hi all,
Im a newbie to Talend and was trying to use to create a transformation. I am trying to create a Dimensional model and have created few dimension tables. Now for loading to Fact table I need to do lookup on Dimension tables before loading. This is where my problem starts.
I have a dimension
Device_dim
DEVICE_KEY DEVICE_NAME
1 Nokia
2 LG
..
..
so on
Now my in my input stream the field where I get the device name is a long string which has the device name somewhere in between. eg.
xyz abc/Nokia-N97:abc def
also the device name can come up anywhere in between the string. i.e. xyz abLG-c/kkjfhsdef
As in perl you can do pattern matching or simply grep to check the avalability of the DEVICE_NAME in the string and return DEVICE_NAME but my transformation is in java.
Im using tMap to do lookup but as the string value in input stream is a long string with the DEVICE_NAME somewhere in between I am not able to create a transformation to do lookup on two values with a common pattern.
Please help as its urgent. For better understanding of my problem please IM me on gtalk: avantasingh@gmail.com
Thanks
AvantA
Community Manager

Re: lookup using pattern matching

Hi
Do a out join on tMap and add a filter condition on output table.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: lookup using pattern matching

Thanks a ton Shong, that worked like magic. But I am facing another issue.
As I said earlier I am trying to load a Fact table and one of foreign key is DEVICE_KEY. Now as I loaded the fact table the way you told me I am able to get all the records satisfying the condition. Also I am able to get the rejected values (using enable/disable output reject) as another output. Now my issue I am facing is that my reject output is in multiples of the no. of devices in the device dimension table.
For Example, suppose I have 10 transactions and 5 device names. Now I use the lookup logic as guided by u and am able to get output where 3 devices matches so I am getting 7 records as output. But in the reject output I am getting 43 records/transactions.
What I deduct from this is that for each 5 devices all 10 transactions are checked and the output is sent as 50 transactions.
What I expected to get was.. if DEVICE_NAME matches then add DEVICE_KEY else add null i.e. no transaction/records should be missed (as in my case I am missing 3 records.)
It would be great if you could guide me how it can be done.
Thanks and Regards,
AvantA
One Star

Re: lookup using pattern matching

Hi
Do a out join on tMap and add a filter condition on output table.
Best regards
Shong

Hi Shong, I really need your help in this. Can youe please guide me through the issue I am facing.
Thanks and Regards,
AvantA
Community Manager

Re: lookup using pattern matching

Hello
What I expected to get was.. if DEVICE_NAME matches then add DEVICE_KEY else add null i.e. no transaction/records should be missed (as in my case I am missing 3 records.)

Please give us an example to explain your problem. What's input records? What's are you expected result?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: lookup using pattern matching

Hello
What I expected to get was.. if DEVICE_NAME matches then add DEVICE_KEY else add null i.e. no transaction/records should be missed (as in my case I am missing 3 records.)

Please give us an example to explain your problem. What's input records? What's are you expected result?
Best regards
Shong

Suppose I have two tables.
Device_Dim_table
======================
DEVICE_KEY DEVICE_NAME
1 Nokia
2 Samsung
---------------------------------------
Input_Table
======================
URI DEVICE_STRING
google.com hi/abcdef1234 Nokia-N97?why
yahoo.com sdgfsgsLGSGDFj /sfkjhkjs
---------------------------------------
Fact Table Output (I am getting)
======================
URI DEVICE_KEY
google.com 1
---------------------------------------
Expected FACT Table output
======================
URI DEVICE_KEY
google.com 1
yahoo.com null
---------------------------------------

Thanks and Regards
AvantA

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads