I currently have a few values in my dataset where the join should work but doesn't work. I have checked the values in Qlik and they are correct and should join. The join should work on the dataset which is marked in bold in the code below, or marked yellow in the attachment. Why doesn't it work
tmp_C8:
LOAD
"Item Trading Code" & '|' & "Color Code" as %ItemColor_GlobalId,
"Item Trading Code" & '|' & "Color Code" as ItemColor_GlobalId_C8,
'FT' as _indSection,
*
Concatenate( tmp_C8 )
LOAD
"Item Trading Code" & '|' & "Color Code" as %ItemColor_GlobalId,
"Item Trading Code" & '|' & "Color Code" as ItemColor_GlobalId_C8,
'APP' as _indSection,
*
Qualify *;
Unqualify '%*';
Unqualify '_*';
NoConcatenate
C8:
Load
*
Resident tmp_C8
;
Drop table tmp_C8;
Outer Join( C8 )
Infa:
LOAD
"Trading Code" & '|' & "Color Code" as %ItemColor_GlobalId,
"Trading Code" & '|' & "Color Code" as ItemColor_GlobalId_Infa,
Date(Date#("Regional Retail Introduction Date", 'DD/MM/YYYY'),'MM/DD/YYYY') as RegionalRetailIntroductionDate,
"Item ID" & '|' & "Color" as ItemColor_GlobalId_SAP,
If(num#([MovAv.4200],'#,00')<>0 AND num#("LSRP-4000",'#,00')<>0 AND trim([MovAv.4200]) & ''<>'' AND ("LSRP-4000")<>0 AND ([MovAv.4200])<>0 AND len(trim(Theme))*len(trim(MovAv.4200))*len(trim("CoO(Purcha"))*len(trim("LSRP-4000")), 'Ready', 'Not ready') as SAPSFMSstatusSalesorder,
What fields are not joining? Have you made sure they are of the same type? Sometimes when this happens there are of different data types, for example one field is interprented as text and the other as number. Try to cast both fields into the same data type.
@AronC PPT: Color and SAP: Color chop off zero's Infa: color code adds and additional 0 in 001 while there is 001 in the original dataset. How can I find out which datatypes are used and how can I change that?
