Search code examples
azure-databricks

withColumn does not return negative value

I am trying to add a column to a dataframe using withColumn. if the reported date or acknoledgement date is null it should return -1 otherwise it should return the difference. I write dataframe to a csv. It adds a new column to the csv, with the date difference as mentioned in otherwise, but does not return -1 if either of the date values is null. the CSV file has blank value for the when clause. What am I doing wrong?

val df_asbreportssv2 = df_asbreportssv1.withColumn(("AckOverdueby"), when(((df_asbreportssv1("nh_reporteddate").isNull)||(df_asbreportssv1("nh_acknowledgementdate").isNull) == "true"), -1 ).otherwise( datediff((df_asbreportssv1("nh_acknowledgementdate")),(df_asbreportssv1("nh_reporteddate")))))

val TempFilePath = "adl://dldataplatformdev1.azuredatalakestore.net/DDS_Learn/DDS_ASB/temp"
df_asbreportssv2.write
                          .mode("overwrite")
                          .format("csv")
                          .option("header", "true")
                          .save(TempFilePath)
Solution

  • The first line of code is not working properly as expected and it needs some refinement as follows. 

    val df_asbreportssv2 = df_asbreportssv1.withColumn(("AckOverdueby"), when((df_asbreportssv1("nh_reporteddate").isNull) || (df_asbreportssv1("nh_acknowledgementdate").isNull), -1 ).otherwise( datediff((df_asbreportssv1("nh_acknowledgementdate")),(df_asbreportssv1("nh_reporteddate")))))

    Hope it helps :)