2 Replies Latest reply: Aug 11, 2014 11:25 AM by Jeremy Oberg RSS

    Replacing Nulls when Joining two tables

      I am wondering if there is an easier/better way to accomplish replacing null values when joining two tables.  Here is the situation:


      I have a dimension table in the database and I want to apply default values for any null value when applying a mapping table to the dimension table.  I need to map several columns so I am not using ApplyMap (although I suppose I could create 5 mapping tables then use them to default the values for each column).


      Using a SQL Query I would simple join the two tables and use the isnull function:





      ,isnull([Client Name Abr],'New Client') as [Client Name Abr]

      ,ISNULL([Client Name]), 'New Client') as  [Client Name]


           inner join Structure s on s.BOB_ID = d.BOB_ID

      Where d.LDR_ENTITY_ID = '08304';


      I am having a harder time finding a simple way to pull this off with Qlikview.  For now, I am loading the dimension table, then left joining th e structure table, and finally loading into the final table using an IF statement to replace the nulls:



      LOAD "BOB_ID",





      Where LDR_ENTITY_ID = '08304';




      LOAD * INLINE [

          BOB_ID, Client Name Abr, Client Name, PC Owner Abr, PC Owner, ClientSort

          AFAS000004, BBT, BBandT Loan Services, LA, Loe Arr, 1

          AFAS000037, BSI, BSI, LA, Loe Arr, 1

          AFAS000012, 5th 3rd, Fifth Third, LA, Loe Arr, 1

          AFAS000015, HomeS, "Home Servicing, LLC", LA, Loe Arr, 1

          AFAS000017, Llive, Lender Live, LA, Loe Arr, 1

          AFAS000021, Ocwen, Ocwen, LA, Loe Arr, 1

          AFAS000022, OWB, One West Bank, LA, Loe Arr, 1

          AFAS000025, PMH, PMH Financial, LA, Loe Arr, 1




      Left Join (Clienttemp) Load


      [Client Name Abr],

      [Client Name],

      [PC Owner Abr],

      [PC Owner],


      Resident ClientStructure;



      drop table ClientStructure;







      ,IF(ISNULL([Client Name Abr]),'New Client',[Client Name Abr]) as [Client Name Abr]

      ,IF(ISNULL([Client Name]), 'New Client', [Client Name]) as [Client Name]

      ,IF(ISNULL([PC Owner Abr]), 'New', [PC Owner Abr]) as [PC Owner Abr]

      ,IF(ISNULL([PC Owner]), 'New Client', [PC Owner]) as [PC Owner]

      ,IF(ISNULL([ClientSort]), 99, [ClientSort]) as [ClientSort]

      Resident Clienttemp;



      DROP Table Clienttemp;


      Is there an easier/better way to do this?