lookup using pattern matching

Highlighted
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
Highlighted
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
Highlighted
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
Highlighted
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
Highlighted
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
Highlighted
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

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog