dusmcpub python
#Source Table Name : Dataframe Name
B2B.ODS_SFC_TPSUSNINFON : df_ODS_SFC_TPSUSNINFON
B2B.ODS_SAP_MATERIALMASTER : df_ODS_SAP_MATERIALMASTER
B2B.MAP_QMD_PART_CATEGORY : df_MAP_QMD_PART_CATEGORY
B2B.ODS_SFCUSNITEM : df_ODS_SFCUSNITEM
B2B.ODS_SFCUSN : df_ODS_SFCUSN
B2B.ODS_SFCUSNDEFECT : df_ODS_SFCUSNDEFECT

#Target : 
#Get df_ODS_SFC_TPSUSNINFON column(USN) to right record column(USN) from df_ODS_SFCUSN
#Scope : 
dusmcpub python How to get it? dusmcpub python
#df_ODS_SFC_TPSUSNINFON column(USN) = df_ODS_SFC_TPSUSNINFON column(CHILDUSN) 
#and df_ODS_SFC_TPSUSNINFON column(USN) word start with 'BZA'
#and CATEGORY = 'S-'(L10)
#df_tmp_0 will be the correct base data for SFC_TPSUSNINFON
df_tmp_mm1 = (df_ODS_SFC_TPSUSNINFON.join(df_ODS_SFCUSNITEM, (df_ODS_SFC_TPSUSNINFON.USN == df_ODS_SFCUSNITEM.CSN)
                                                             &(df_ODS_SFC_TPSUSNINFON.PLANT == df_ODS_SFCUSNITEM.PLANT), 'inner')
                                     .join(df_ODS_SFCUSN, (df_ODS_SFCUSNITEM.USN == df_ODS_SFCUSN.USN)
                                                         &(df_ODS_SFCUSNITEM.PLANT == df_ODS_SFCUSN.PLANT), 'inner')
                                     .join(df_ODS_SAP_MATERIALMASTER, (df_ODS_SFCUSN.UPN == df_ODS_SAP_MATERIALMASTER.MATNR)
                                                                     &(df_ODS_SFCUSN.PLANT == df_ODS_SAP_MATERIALMASTER.WERKS), 'left')
dusmcpub python How to use it? dusmcpub python
                                     .where((df_ODS_SFC_TPSUSNINFON.USN == df_ODS_SFC_TPSUSNINFON.CHILDUSN)
                                                              &(df_ODS_SFC_TPSUSNINFON.USN.like('BZA%'))
                                                              &(df_ODS_SAP_MATERIALMASTER.ZZCATE == 'S-'))
                                     .withColumn('USN_NEW', df_ODS_SFCUSN.USN)
                                     .withColumn('USN_MM', df_ODS_SFC_TPSUSNINFON.USN)
                                     .withColumn('CHILDUSN_MM', df_ODS_SFC_TPSUSNINFON.CHILDUSN)
                                     .withColumn('PLANT_MM', df_ODS_SFC_TPSUSNINFON.PLANT)
                                     .select('USN_NEW', 'USN_MM','CHILDUSN_MM', 'PLANT_MM'))
dusmcpub python