topic Re: Replace Null Values after multiple outer and left joins in QlikView

Replace Null Values after multiple outer and left joins

nigelapt — Sat, 11 Jun 2016 18:16:54 GMT

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

swuehl — Sun, 12 Jun 2016 12:14:52 GMT

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

nigelapt — Mon, 13 Jun 2016 19:49:14 GMT

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 Device, Device as HWDevice, HWRegion, HWCountry, HWCity, HWModel
FROM

(ooxml, embedded labels, table is Sheet1);

DraftSWInventory:
LOAD Device, Device as SWDevice, AppRegion, AppWorkerGrp
FROM

(ooxml, embedded labels, table is Sheet1);

DraftSCOMInventory:
LOAD Device, Device 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:

Device	HWDevice	HWRegion	HWCountry	HWCity	HWModel	SWDevice	AppRegion	AppWorkerGrp	SCOMDevice	SCOMAgent	SCOMStatus
sbc1001	sbc1001	EMEA	UK	London	HP ABC	sbc1001	EMEA	Worker Group 1	sbc1001	AG1234	Healthy
sbc1002	sbc1002	EMEA	UK	London	HP ABC	sbc1002	EMEA	Worker Group 2	sbc1002	AG1234	Healthy
sbc1003	sbc1003	EMEA	UK	Belfast	HP ABC	sbc1003	EMEA	Worker Group 3	sbc1003	AG1234	Healthy
sbc1004	sbc1004	EMEA	Poland	Warsaw	HP DEF	sbc1004	EMEA	Worker Group 4	sbc1004	AG1234	Healthy
sbc1005	sbc1005	EMEA	Poland	Warsaw	HP DEF	sbc1005	EMEA	Worker Group 5	sbc1005	AG1234	Healthy
sbc1006	sbc1006	NAM	USA	Texas	HP ABC	sbc1006	EMEA	Worker Group 6	sbc1006	AG1234	Critical
sbc1007	sbc1007	NAM	USA	New York	HP DEF	sbc1007	EMEA	Worker Group 7	Missing	-	-
sbc1011	Missing	-	-	-	-	sbc1011	EMEA	Worker Group 11	Missing	-	-
sbc1012	Missing	-	-	-	-	sbc1012	EMEA	Worker Group 12	Missing	-	-
sbc1013	Missing	-	-	-	-	sbc1013	EMEA	Worker Group 13	Missing	-	-
sbc1014	Missing	-	-	-	-	sbc1014	EMEA	Worker Group 14	Missing	-	-
sbc1015	Missing	-	-	-	-	sbc1015	EMEA	Worker Group 15	Missing	-	-
sbc1008	sbc1008	NAM	Canada	Toronto	HP ZAD	Missing	-	-	Missing	-	-
sbc1009	sbc1009	APAC	Jordan	Oman	HP ABC	Missing	-	-	Missing	-	-
sbc1010	sbc1010	APAc	UAE	Tikrit	HP DEF	Missing	-	-	Missing	-	-

Re: Replace Null Values after multiple outer and left joins

swuehl — Mon, 13 Jun 2016 20:12:33 GMT

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

nigelapt — Tue, 14 Jun 2016 20:51:29 GMT

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

swuehl — Tue, 14 Jun 2016 21:24:26 GMT

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.