Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 *
Left Join
Load *
From \\SupportTeam
Outer Join
Load *
Left Join
Load *
From \\Monitoring Agent Health Status
Left Join
Load *
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
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:
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 | - | - |
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.
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?
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
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.