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:

       

      Select

      BOB_ID

      ,BOB_DESC

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

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

      FROM "DM_LEDGER"."BLOCK_OF_BUSINESS" d

           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:

       

      Clienttemp:

      LOAD "BOB_ID",

        "BOB_DESC";

      SQL SELECT "BOB_ID",

          "BOB_DESC"

      FROM "DM_LEDGER"."BLOCK_OF_BUSINESS"

      Where LDR_ENTITY_ID = '08304';

       

       

      ClientStructure:

      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

      BOB_ID,

      [Client Name Abr],

      [Client Name],

      [PC Owner Abr],

      [PC Owner],

      ClientSort

      Resident ClientStructure;

       

       

      drop table ClientStructure;

       

       

      Client:

      NoConcatenate

      Load

      BOB_ID, BOB_DESC

      ,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?