I have row values from 2 different sources, i want to compare both the source table & identify the hostname missing in other table

[vijayraj0608]

I have row values from 2 different sources, i want to compare both the source table & identify the hostname missing in other table.

Both the data source are same as above, one has some additions, need to identify the additional hostname & list in the table.

Please suggest.

[julian_rodriguez]

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:

1. 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];

2. 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;

3. 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;

4. 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.

5. 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)

6. 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.

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

[anat]

you can give a try by using ApplyMap function like lookup in excel ..

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunction...