# Perform the joins
df_joined = df_pln_mbr_elig.alias("MEPE") 
    .join(df_cmn_cd.alias("cd"), 
          (col("MEPE.C1_Pd_Cgy_Cd_Sk") == col("cd.Cmn_Cd_Sk")) & 
          (col("cd.Vld_to_ts") == '9999-12-31T23:59:59.999+0000'), 
          "inner") 
    .join(df_pln_mbr_dtl.alias("mem"), 
          (col("MEPE.Pln_Mbr_Sk") == col("mem.Pln_Mbr_Sk")) & 
          (col("mem.Vld_To_Ts") == '9999-12-31T23:59:59.999+0000'), 
          "inner") 
    .join(df_pln_mbr_enrl_arr.alias("MEES"), 
          (col("MEPE.Pln_Mbr_Sk") == col("MEES.Pln_Mbr_Sk")) & 
          (col("MEES.Vld_To_Ts") == '9999-12-31T23:59:59.999+0000') & 
          ((col("MEES.Mbr_Eff_Dt").between(col("MEPE.Elig_Eff_Ts"), col("MEPE.Elig_Tmt_Ts"))) | 
           (col("MEES.Mbr_Tmt_Dt").between(col("MEPE.Elig_Eff_Ts"), col("MEPE.Elig_Tmt_Ts")))), 
          "left")

# Apply the transformations and select the required columns
df_result = df_joined.select(
    col("MEPE.Grp_Id").alias("Grp_Id"),
    col("mem.Pin_Mbr_Id").alias("Pin_Mbr_Id"),
    col("cd.Cd").alias("ProductCategoryCode"),
    when(col("MEPE.Elig_Ind") == "some_value", "then_value").otherwise("else_value").alias("MemberEligibleFlag"),
    col("MEPE.Elig_Eff_Ts").alias("Elig_Eff_Ts"),
    col("MEPE.Elig_Tmt_Ts").alias("Elig_Tmt_Ts"),
    substring(col("MEES.Qual_Hp_Id"), 8, 4).alias("Product"),
    when(substring(col("MEES.Qual_Hp_Id"), 8, 4).isin(['0020', '0050']), "desired_value").otherwise("other_value").alias("ConditionCheck")
).distinct()

# Filter the result
df_filtered = df_result.filter(
    (col("Grp_Id").isin(6, 9, 10, 17, 27)) &
    (col("MEPE.Elig_Ind") == "some_condition") &
    (col("cd.Cd") == 'M') &
    (col("MEPE.Vld_To_Ts") == '9999-12-31T23:59:59.999+0000')
)

# Show the result
df_filtered.show()

had less cnumber o f lines make it easy yo fsknndlansdfnasdfn . had less cnumber o f lines make it easy yo fsknndlansdfnasdfn .had less cnumber o f lines make it easy yo fsknndlansdfnasdfn .had less cnumber o f lines make it easy yo fsknndlansdfnasdfn .had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn

had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn had less cnumber o f lines make it easy yo fsknndlansdfnasdfn