To compare row values from two different sources and identify the hostnames that are missing in one of the tables using Qlik Sense, you can follow these steps:
- Load Both Data Sources: Load both tables into Qlik Sense. For example, let's call them
SourceA and SourceB.
SourceA:
LOAD
[S.No],
Hostname,
Type
FROM [DataSourceA];
SourceB:
LOAD
[S.No],
Hostname,
Type
FROM [DataSourceB];
- Left Join to Find Missing Hostnames: Perform a left join to find out which hostnames are present in
SourceA but missing in SourceB.
Left Join (SourceA)
LOAD
Hostname as HostnameB
RESIDENT SourceB;
- Create a Flag for Missing Hostnames: After the join, you can create a flag that identifies whether the hostname is missing in
SourceB.
LOAD *,
If(IsNull(HostnameB), 'Missing', 'Present') as Status
RESIDENT SourceA;
-
Visualize the Data: Now you can create a table in your Qlik Sense app where you can add the Hostname and the Status to see which ones are missing.
-
Alternative - Set Analysis for Comparison: If you prefer to keep the tables separate, you can use set analysis in a chart expression to identify discrepancies.
For instance, to count the number of hostnames in SourceA not found in SourceB, you can use:
Count({$<Hostname = {"=not wildmatch(Hostname, $(=concat(Hostname, '|')))"}>} Hostname)
- Load Script for Full Outer Join: If you need to identify hostnames that are missing in both sources, you could do a full outer join in the script:
FullOuterJoinTable:
LOAD Hostname,
Type
FROM [DataSourceA]
WHERE NOT Exists(Hostname);
CONCATENATE (FullOuterJoinTable)
LOAD Hostname,
Type
FROM [DataSourceB]
WHERE NOT Exists(Hostname);
This script creates a new table, FullOuterJoinTable, which contains all hostnames from both sources, without duplicates.
- Create a List of Missing Hostnames: You can create a straight table with the
Hostname and Status to show which hostnames are missing from SourceB. Alternatively, you could create two straight tables, one showing hostnames only in SourceA and another showing hostnames only in SourceB.
Remember to replace DataSourceA and DataSourceB with the actual paths to your data sources.
Please note that the exact implementation may vary depending on the specifics of your data and the Qlik Sense version you are using. If you encounter any issues, consider providing more details or sharing a snippet of the load script for personalized assistance.
