tMap join 'like'

[solquest]

Hey there.

I have a csv file (file_1) and I need to write its data on another csv file only if the code (example 'C01') in file_1 is contained in a field (example 'C01-02-03') of a db table.

I've set tDBInput as lookup for TMap and I've found that for using a 'like' operator, in tMap I shouldn't set key column in my lookup while using lookup filter instead.

So in lookup expression filder I've set

row21.category.startsWith(row18.code)

where row21 is the lookup and row18 the input file.

In Match model I'd set 'First match' -I only want to know if that code is present, but when I set it I get the message "The lookup table should have at least one key expression filled" and when I save it, it gets changed to 'All rows'.

But this means that in the output csv file I'm getting tons of data.

How should I manage this?

Thanks.

[solquest]

For adding more details, in the db table of row21 I can have

id, code

1, C01

2, C01

3, C01-01

4, C01-01-11

So in from the input csv file I have C01 or C01-01 or C01-01-11 they all should go to the output file (as many time as they are in the input csv file).

[gjeremy1617088143]

HI, so it cant work you have to define at least one lookup column, if you try to filter on the same file use your filter expression in the output of the tMap and don't use a join.

Send me Love and Kudos

[solquest]

Thanks.

I'm not trying to filter on the same file, I want to filter data on input file depending if they are (but not on exact match, see below) on a db table.

In the input file I have

code

C01

C01-11

C01-11-111

C02

C02-11

while in db table I have

product; category

1; C01-11-111

2; C01-11-111

3; C01-11-111

So I want to output only the records that have db_table.category.startsWith(input_file.code).

So for the example, the output should be

code

C01

C01-11

C01-11-111

I can't join input_file.code = db_table.category, cause in this way I'd miss C01 and C01-11

How can I do it?

[gjeremy1617088143]

I think , First you read the file with CODE then you iterate with tFlowToIterate (to stock the code in a globalVar) and for each iteration you read the db with the globalVar in the query :

"select * from your table where category like'" + (String)globalMap.get(your global var) + "%'"

and you append all the result in a buffer or a file.

[Prakhar1]

hi @solquest solquest​ ,

try using attached solution.

[solquest]

Thanks.

That one is not working, cause in row1.C_ODE I can have C01 while in row2.C_ODE_1 I have C01-01-01 and that C01 should go in the output.

I tried writing

row2.C_ODE_1.startsWith(row1.C_ODE) ? row2.C_ODE_1 : "0"

in the expression key for row2.C_ODE_1, but seems I can't use row2.C_ODE_1 for joining row2.C_ODE_1...

[gjeremy1617088143]

@solquest, try my solution with the tflowtoiterate and the globalVar in the sql query.

[manodwhb]

@solquest solquest​ ,try to contains function instead of startsWith and let me know.

Thanks,

Manohar

[Prakhar1]

Just for my understanding :

In row1.C_ODE data can be like

C01

C02

and in row2.C_ODE data can be like

C01-0210, "Some data"

C02212, "other data"

And you want if row2.C_ODE starts with "C01", then output the row1.C_ODE which will look like below

C01, "Some data"

If this is true then my code is doing the same thing.

In the output you just need to select the row1.C_ODE data link