Using Case statement in Spark Dataframe in join condition

Question

How can i use case condition while joining two dataframes in spark.

    var date_a = s"CASE WHEN month(to_date(from_unixtime(unix_timestamp(dt, 'dd-MM-yyyy')))) 
    IN  (01,02,03) THEN CONCAT(CONCAT(year(to_date(from_unixtime(unix_timestamp(dt, 'dd-MM-yyyy'))))-1,'-')
    ,substr(year(to_date(from_unixtime(unix_timestamp(dt, 'dd-MM-yyyy')))),3,4)) 
    ELSE CONCAT(CONCAT(year(to_date(from_unixtime(unix_timestamp(dt, 'dd-MM-yyyy')))),'-'),
    SUBSTR(year(to_date(from_unixtime(unix_timestamp(dt, 'dd-MM-yyyy'))))+1,3,4)) END"

    val gstr1_amend = df1.join(gstr1_amend_lkup_data, df1("date_b") === df2(date_a))

But am getting error case is not a column.

Are you still facing this issue ?

s.polam
– s.polam

2020-05-21 10:31:27 +00:00
Commented May 21, 2020 at 10:31 — s.polam
– s.polam, Commented May 21, 2020 at 10:31

supreeth · Accepted Answer · 2020-08-31 15:15:25Z

1

I had a similar situation with a minor diff, I wanted to use column from second data frame in case when column from first column is blank, and this is to be done only on joining. Couldn't use a case, however joined on another key column and used case in filter. Isn't elegant solution, but works.

answered Aug 31, 2020 at 15:15

supreeth

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

s.polam · Accepted Answer · 2020-05-19 07:34:38Z

0

Instead of adding case statement in joining condition, add all conditions using when & otherwise functions inside withColumn and then use same column in join condition like below.

val df2 = somedf
.withColumn("date_a",when([...]).otherwise([...])) // [...] is your case statement logic

val gstr1_amend = df1.join(df2, df1("date_b") === df2("date_a"))

edited May 19, 2020 at 7:34

answered May 19, 2020 at 7:28

s.polam

10.4k2 gold badges17 silver badges29 bronze badges

Collectives™ on Stack Overflow

Using Case statement in Spark Dataframe in join condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related