Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nigelapt
Contributor III
Contributor III

Replace Null Values after multiple outer and left joins

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

5 Replies
swuehl
MVP
MVP

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

nigelapt
Contributor III
Contributor III
Author

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

(
ooxml, embedded labels, table is Sheet1);

DraftSWInventory:
LOAD DeviceDevice as SWDeviceAppRegionAppWorkerGrp
FROM

(
ooxml, embedded labels, table is Sheet1);

DraftSCOMInventory:
LOAD DeviceDevice as SCOMDevice, SCOMAgent, SCOMStatus
FROM

(
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--

swuehl
MVP
MVP

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.

nigelapt
Contributor III
Contributor III
Author

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?

swuehl
MVP
MVP

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.