Linking fields

Report Inappropriate Content · ‎2016-04-25

I've been given the following syntax to use when extracting data from an SQL database...

SQL SELECT FORMRESULTKEY, FormResultDate, IdentityReference, IdentityCompany, JBH_COMPANY, JBH_CONTRACT_REF, JBH_AREA, CNT_DESCRIPTION

FROM "PM_DATAMART" (NOLOCK)

JOIN "WM_JOB_HEADER" (NOLOCK)

ON JBH_JOB_REF COLLATE SQL_Latin1_General_CP1_CI_AS = CONVERT(VARCHAR(7),IdentityReference)

AND JBH_COMPANY COLLATE SQL_Latin1_General_CP1_CI_AS = IdentityCompany

JOIN "Live_FHG_TotalRepairs".dbo."WM_CONTRACT" (NOLOCK)

ON JBH_COMPANY = CNT_COMPANY

AND JBH_CONTRACT_REF = CNT_CODE

WHERE FormReference LIKE '%HP HSC%'

AND FormElementReference = 'HPYESNO'

AND FRAValue = 'YES';

I've highlighted the text I'm struggling with. JBH_JOB_REF is a field from one table and IdentifyReference is the same field from another table and should link, the difference being JBH_JOB_REF is for example, 1000003 and IdentityReference is 1000003[1] which is why I'm surmising the 'CONVERT' function is there so that it includes the first 7 characters only. The extract works fine and doesn't error but there's no link between the two.

Is the script being used correctly for QlikView? Would love it if someone could assist.

Many thanks.

Gysbert_Wassenaar · ‎2016-04-25

The SQL statement is executed by your database server, not Qlikview. If the SQL statement doesn't do what you want then ask your local friendly database administrator to help you troubleshoot it.

talk is cheap, supply exceeds demand

marcus_sommer · ‎2016-04-26

Maybe you could apply a preceeding load and do the transforming/cleaning there like:

Load SQL SELECT FORMRESULTKEY, FormResultDate, num(left(IdentityReference, 7)) as IdentityReference, IdentityCompany, JBH_COMPANY, JBH_CONTRACT_REF, JBH_AREA, CNT_DESCRIPTION;