Table not joining correctly despite field names matching

[martynwb]

I have an app with two tables that need to be joined via the transaction table. There is a foreign key field in both tables which is meant to relate them: %Key_HOST_312E40B6043C9FFE As QualysHostKey.

However, these tables are not relating. No information in either table is relating to information in the other table, even though I've checked the raw data and all of the key information is there and matches. 

The load script is below. Any help would be appreciated:

Concatenate(Transactions)
LOAD
'Daily' As DataType,
'QUALYS_Detection_Daily' as TransactionSource,
QualysHostID,
QualysHostIP,
QualysHostTrkMethod,
QualysHostOS,
QualysHostLastScan,
QualysHostScanDuration,
%Key_HOST_312E40B6043C9FFE As QualysHostKey,
QualysHostLastVMScanDate,
QualysHostLastVMScanDur,
QualysHostDNS,
QualysHostOSCPE,
QualysHostNetBios,
QualysHostQGHostID,
QualysHostLastVMAuthScanDate,
QualysHostLastVMAuthScannedDur,
QualysHostHostname,
QualysHostDomain,
QualysHostFQDN,
%Key_HOST_LIST_VM_DETECTION_OUTPUT_087173C108D6BD52,
SubDivision,
ApplyMap('MAP_DIV',SubDivision,'NoDivision') as Division,
ApplyMap('MAP_PILLAR',SubDivision,'NoDivision') as Pillar,
QualysHostPublicProvate,
QualysHostType,
QualysHostEOL
FROM [lib://QVD_S1/InfoSec/Qualys_Extract_Host_ALL.qvd]
(qvd);

Concatenate(Transactions)
LOAD
'Daily' As DataType,
'QUALYS_HOST_Daily' as TransactionSource,
QualysID,
QualysDetectType,
QualysDetectSeverity,
Pick(WildMatch(QualysDetectSeverity, '5', '4', '3', '2', '1'),'14', '30', '60', '90', '90') as QualysDetectSevDaysToFixReq,
Pick(WildMatch(QualysDetectSeverity,'5','4','3','2','1'),'Critical','High','Moderate','Low','Informational') As QualysDetectSeverityDesc,
QualysDetectPort,
QualysDetectProtocol,
QualysDetectSSL,
QualysDetectResults,
QualysDetectStatus,
QualysDetectFirstFoundDateTime,
QualysDetectFirstFoundTimeStamp,
QualysDetectFirstFoundShortDate,
QualysDetectLastFoundDateTime,
QualysDetectTimesFound,
QualysDetectLastTestDateTime,
QualysDetectLastUpdateDateTime,
QualysDetectIsIgnored,
QualysDetectIsDisabled,
QualysDetectLastProcessedDateTime,
QualysDetectLastFixedDateTime,
QualysDetectLastFixedDateTime - QualysDetectFirstFoundDateTime As QualysDetectDaysToFix,
if(IsNull(QualysDetectLastFixedShortDate),
if((today() - QualysDetectFirstFoundDateTime) > Pick(WildMatch(QualysDetectSeverity, '5', '4', '3', '2', '1'), '14', '30', '60', '90', '90'), 0, 1),
if(QualysDetectLastFixedDateTime - QualysDetectFirstFoundDateTime > Pick(WildMatch(QualysDetectSeverity, '5', '4', '3', '2', '1'),'14', '30', '60', '90', '90') ,0,1) ) As QualysDetectWithinPolicy,
QualysDetectLastFixedShortDate,
QualysDetectFQND,
%Key_HOST_312E40B6043C9FFE As QualysHostKey
FROM [lib://QVD_S1/InfoSec/Qualys_Extract_Detection.qvd]
(qvd);

[martynwb]

Thanks. Then I still need to resolve the synthetic key issue and it will work.

For the below script, a synthetic key is created for Subdivision+Pillar+Division. I need to remove that synthetic key but I can't work out why it's being generated in the first place:

Qualys:
NoConcatenate
LOAD
//*,
//'Daily' As DataType,
//'QUALYS_Detection_Daily' as TransactionSource,

QualysHostID,
QualysHostIP,
QualysHostTrkMethod,
QualysHostOS,
QualysHostLastScan,
QualysHostScanDuration,

Text(%Key_HOST_312E40B6043C9FFE) As QualysForeignKey,

QualysHostLastVMScanDate,
QualysHostLastVMScanDur,
QualysHostDNS,
QualysHostOSCPE,
QualysHostNetBios,
QualysHostQGHostID,
QualysHostLastVMAuthScanDate,
QualysHostLastVMAuthScannedDur,
QualysHostHostname,
QualysHostDomain,
QualysHostFQDN,

%Key_HOST_LIST_VM_DETECTION_OUTPUT_087173C108D6BD52,
SubDivision,

ApplyMap('MAP_DIV',SubDivision,'NoDivision') as Division,
ApplyMap('MAP_PILLAR',SubDivision,'NoDivision') as Pillar,

QualysHostPublicProvate,
QualysHostType,
QualysHostEOL

FROM [lib://QVD_S1/InfoSec/Qualys_Extract_Host_ALL.qvd]
(qvd);

Any ideas?

[marcus_sommer]

It depends on your datamodel + data + the requirements what should be displayed in the end what you need to do. Two measures are often used in such scenarios. One is to rename some of the fields, for example those from your applymap() and the other is to combine all fields into a single field with something like: F1 & '|' F2 & '|' & F3 as KEY.

Beside this that you get synthetic keys is not caused from a single load else at least from two loads. Therefore this load above might be completely ok. and you may need to change the other loadings.

- Marcus

[martynwb · Accepted solution]

For anybody reading this and having similar issues. I'll summarise how I resolved mine.

Essentially I wanted to join two tables together (concatenate wouldn't work) and then concatenate them to my Transactions table (where all of my data is stored). 

So I used NoConcatenate on the table to join, then fed the resulting table into Transactions via Concatenate, before dropping the originally joined table. This removed the synthetic keys resulting from the joins.