5 Replies Latest reply: Jun 14, 2016 5:24 PM by Stefan Wühl RSS

    Replace Null Values after multiple outer and left joins

    Nigel Taylor

      Hi Everyone

      I am having trouble loading data from multiple data sources, and then replacing the null values in the resulting table.

      I have tried loading the data from the table into a second table, replacing null values, and then dropping the first table.

      However I seem to be getting duplicate records and not sure where I am going wrong.

       

      I have used a mixture of Left Joins to only add data to existing records, and OuterJoins where I need to add all records where they don't exist.  The data is being brought in as follows:

       

      DraftInventory:

      Load *

      From \\...HardwareInventory

       

      Left Join

      Load *

      From \\SupportTeam

       

      Outer Join

      Load *

      From \\MonitoringAgentDetails

       

      Left Join

      Load *

      From \\Monitoring Agent Health Status

       

      Left Join

      Load *

      From \\MonitoringAgentRole

       

      Outer Join

      Load *

      From \\ApplicationInventoryEMEA

      concatentate

      Load *

      From \\ApplicationInventoryAPAC

      Load *

      From \\ApplicationInventoryNAM

       

       

      FinalInventory:
      NoConcatenate
      Load
        All fields from all tables
        if(isnull(F3), 'Y', F3) as F3 -  for each field which needs the null value replaced
      Resident DraftInventory;

      Drop Table Draft Inventory;

       

      Any advice on the correct method would be greatly appreciated.

      Thanks

      Nigel

        • Re: Replace Null Values after multiple outer and left joins
          Stefan Wühl

          We can only guess what's happening without having more information about the tables and field values involved.

           

          The last RESIDENT table LOAD can't duplicate records, so we can concentrate on the JOINs.

           

          A JOIN, regardless if it is a a LEFT or OUTER JOIN, may duplicate records, depending on the key fields.

           

          For example, if SupportTeam table shows multiple records for the same team, the JOIN will duplicate records in the JOIN with HardwareInventory.

           

          You may want to replace a JOIN, where possible, with a MAPPING table approach, or just keep the tables linked in the data model:

          Don't join - use Applymap instead

          To Join or not to Join

            • Re: Replace Null Values after multiple outer and left joins
              Nigel Taylor

              Thanks Swuehl.  I want to bring the data in as 3 separate tables with a key field "Device", and check that all 3 tables have the same number of rows and matching devices.   have tried to use ApplyMap, but I don't understand how to get the data back out of the Resident table.  This is my script.  The Mapping piece is wrong.

               

              DraftHWInventory:
              LOAD DeviceDevice as HWDeviceHWRegionHWCountryHWCityHWModel
              FROM
              [C:\Users\Nigel\Desktop\HardwareRegister.xlsx]
              (
              ooxml, embedded labels, table is Sheet1);

              DraftSWInventory:
              LOAD DeviceDevice as SWDeviceAppRegionAppWorkerGrp
              FROM
              [C:\Users\Nigel\Desktop\ApplicationRegister.xlsx]
              (
              ooxml, embedded labels, table is Sheet1);

              DraftSCOMInventory:
              LOAD DeviceDevice as SCOMDevice, SCOMAgent, SCOMStatus
              FROM
              [C:\Users\Nigel\Desktop\Monitoring.xlsx]
              (
              ooxml, embedded labels, table is Sheet1);

               

               

              MissingMap:
              MAPPING LOAD
              null(), 'MISSING!' AutoGenerate 1;

              MAP HWDevice using MissingMap;
              MAP SWDevice using MissingMap;
              MAP SCOMDevice using MissingMap;

              Inventory:
              NoConcatenate   // This is important! We want a new table!
              LOAD * RESIDENT drafthwinventory;
              DROP TABLE drafthwinventory;  // Drop the original table

               

              This is what I need the table to look like at the end:

                 

              DeviceHWDeviceHWRegionHWCountryHWCityHWModelSWDeviceAppRegionAppWorkerGrpSCOMDeviceSCOMAgentSCOMStatus
              sbc1001sbc1001EMEAUKLondonHP ABCsbc1001EMEAWorker Group 1sbc1001AG1234Healthy
              sbc1002sbc1002EMEAUKLondonHP ABCsbc1002EMEAWorker Group 2sbc1002AG1234Healthy
              sbc1003sbc1003EMEAUKBelfastHP ABCsbc1003EMEAWorker Group 3sbc1003AG1234Healthy
              sbc1004sbc1004EMEAPolandWarsawHP DEFsbc1004EMEAWorker Group 4sbc1004AG1234Healthy
              sbc1005sbc1005EMEAPolandWarsawHP DEFsbc1005EMEAWorker Group 5sbc1005AG1234Healthy
              sbc1006sbc1006NAMUSATexasHP ABCsbc1006EMEAWorker Group 6sbc1006AG1234Critical
              sbc1007sbc1007NAMUSANew YorkHP DEFsbc1007EMEAWorker Group 7Missing--
              sbc1011Missing----sbc1011EMEAWorker Group 11Missing--
              sbc1012Missing----sbc1012EMEAWorker Group 12Missing--
              sbc1013Missing----sbc1013EMEAWorker Group 13Missing--
              sbc1014Missing----sbc1014EMEAWorker Group 14Missing--
              sbc1015Missing----sbc1015EMEAWorker Group 15Missing--
              sbc1008sbc1008NAMCanadaTorontoHP ZADMissing--Missing--
              sbc1009sbc1009APACJordanOmanHP ABCMissing--Missing--
              sbc1010sbc1010APAcUAETikritHP DEFMissing--Missing--

               

               

                • Re: Replace Null Values after multiple outer and left joins
                  Stefan Wühl

                  You can't map a NULL value, nor can you JOIN on a key field with NULL.

                   

                  I haven't really understood where you do the JOIN of the three tables in the last sample code.

                   

                  If you want to replace a NULL in a field after JOINing two tables, a common approach would indeed a subsequent RESIDENT LOAD with something like

                   

                       If(Len(Trim(FIELD)), FIELD, 'Missing') as FIELD,

                   

                  similar to what you did in your original post.

                   

                  But this would not explain your duplicate records, so I assumed we are looking into that.

                    • Re: Replace Null Values after multiple outer and left joins
                      Nigel Taylor

                      Swuehl, thanks for reply.

                      Qlikview is making its own join using the common 'Device' key field.  Its merging the results of all three tables into one associative data model which is what I need, and displaying '-' for fields where there is no match.

                      For some fields in the model I want to replace '-' with more meaningful text.

                       

                      When I look at the results from each table, there are no missing items.  Its only when Qlikview joins them together in memory that I see where the data does not match up.

                       

                      Can't I write the results of the 3 table merge to a new table, and drop the original 3.  And in doing so replace the '-' with a different text?

                        • Re: Replace Null Values after multiple outer and left joins
                          Stefan Wühl

                          Nigel, it's getting late in here, so apologies if I fail to understand completely.

                           

                          As far as I understand, you are not coping with NULLs in field, but with missing values when two or more tables are involved and dynamically joined by the associative logic.

                           

                          If this is the case, I think there is no way to just reload a single table and replace these NULL or missing values in an existing field with a different.

                          You would need to change your data, i.e. add records to the tables to create the missing links in your model.

                           

                          I am not sure I understood you last two sentences. Do you want to change your data model completely to overcome this issue?

                           

                          Maybe have a look at

                          NULL handling in QlikView

                           

                           

                          If you just want to change the display in a table chart (e.g. pivot table), you can also try to set the option to fill in missing values and change the text symbol to something else (instead of '-') on presentation tab.