Along with join ,use some condition

KNIME Analytics Platform
1

Hi ,

How can we write if else condition along with with join query in Knime.
For Eg-I have two tables
Table A with columns name,city,addr1,addr2

Table B with columns name,city,addr1,addr2
read the column B.addr2 if below join gives some records
select A.addr1,B.addr1 from
tableA A left outer join
tableB B
on (A.name = B.name and A.city = B.city)
else if above join gives null values, get any data from column A.addr2 before prefix-Ay,Bc,Dt.

2

You could use a combination of CASE WHEN and SUBSTR() commands in SQL. For further manipulations, you should see what your SQL database would support. You might also try to generate it with generic KNIME DB nodes. Not sure if that would be sufficient.

kn_example_hive_load_files_case_when.knwf (38.4 KB)

DROP TABLE IF EXISTS default.table_join_c;

CREATE TABLE default.table_join_c
AS SELECT

CASE WHEN t1.Column1 IS NOT NULL
THEN t1.Column1 ELSE t2.Column1 END AS Column1
, CASE WHEN t1.Column1 IS NOT NULL
THEN t1.Id ELSE t2.Id END AS Id

, CASE WHEN t1.Column1 IS NOT NULL
THEN t1.aname
ELSE SUBSTR(t2.name_new,1,3) END AS my_new_name
, t1.aname

FROM default.table_a t1

FULL OUTER JOIN
default.table_b t2
ON t1.aname = t2.name
AND t1.Id = t2.Id
;

4 Likes
3

Thanks ,case when worked exactly what I was looking for:), but one thing substrr() will not work in , as I ma looking for prefix for specif strings, Do we have anything like split function or any regex function?

4

Hi there @analytics_sharma,

I have seen you opened another topic for searching specific prefixes. Please link it in the future in order to keep out forum tidy and for users to easier find answers. EDIT: And this topic issue originated from here…

Additionally feel free to mark reply as solution for same above mentioned purposes.

SolutionButton

Br,
Ivan

5

Hive does support regular expressions to a certain extent. Functions are replace and extract.

I am not sure about the implementation in the local big data environment though.

To develop and test your regex you can use

Or you might just ask @armingrudd for help :slight_smile:

2 Likes
6

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.