Retrieve the valid Party ids from the Latest record DataFrame based on lookup from Invalid Party DataFrame

Silpa Pinjala 0
Viewed 26

Input:

1.Latest Party Records (Primary Key : prty_id, Latest Record identified using : lst_upd_dt)

prty_id country role    lst_upd_dt
P1  IN  Partner 2022/03/01
P2  JP  VSI 2022/01/01
P3  CS  Vendor  2021/05/18
P4  US  Customer    2022/03/12
P5  CA  Partner 2022/10/01
P6  IN  Customer    2019/03/01
P7  CN  Vendor  2022/02/01
P8  BZ  Vendor  2020/09/15

Invalid party id: Invalid Party Id Records

prty_id
P1
P7
P4

Required output is: Valid Party Ids from Latest Record.

prty_id country role    lst_upd_dt
P2  JP  VSI 2022/01/01
P3  CS  Vendor  2021/05/18
P5  CA  Partner 2022/10/01
P6  IN  Customer    2019/03/01
P8  BZ  Vendor  2020/09/15

I am done the code using filter condition like below:

val valid_id=new_part_data.filter($"prty_id"=!="P1")
  .filter($"prty_id"=!="P4").filter($"prty_id"=!="P7").show()

But requirement is: Invalid parties should not be filtered based on hard coding, they should be either from parameter file. how to use this to get the output?

apache-spark scala
1 Answers

You can do that using a left anti join:

val valid_id = new_part_data
  .join(
    right = invalid_id,
    usingColumns = Seq("prty_id"),
    joinType = "left_anti"
  )

valid_id.show()
// +-------+-------+--------+----------+
// |prty_id|country|    role|lst_upd_dt|
// +-------+-------+--------+----------+
// |     P2|     JP|     VSI|2022/01/01|
// |     P3|     CS|  Vendor|2021/05/18|
// |     P5|     CA| Partner|2022/10/01|
// |     P6|     IN|Customer|2019/03/01|
// |     P8|     BZ|  Vendor|2020/09/15|
// +-------+-------+--------+----------+

The left anti join will keep rows from the left dataframe (new_part_data), for which the right dataframe (invalid_id, containing the invalid partie ids) do not have a corresponding value in the column prty_id.

leleogere0
Related