Combine 000 and 0000 inclusion in one join

[KirstenKa]

Currently my join looks like this, under PPT and SAP there are 000 added to prevent that leading zero's are removed. However now I also have a case where I have  0000 (4 zero's) How can I resolve this in my join?                                             Please note: I have remove the FROM.....  only in this example to make the code more clear. See also attachment for the code for joins

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,

              *

Outer Join( C8 )

PPT:

LOAD

    "Trading code" & '|' & Num( "Color" , '000' )                               as %ItemColor_GlobalId,     

    "Trading code" & '|' & "Color"                             as ItemColor_GlobalId_PPT, 

    *

Outer Join( C8 )

SAP:

LOAD

   "Item ID" & '|' & Num( "Color" , '000' )               as %ItemColor_GlobalId,             

    "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,

              *

[AronC]

Do you want 0001 and 00001 to be interpreted as same? If you do I would just floor both fields and join as integer values.
If you want them to be interpreted as two different values I would cast the fields as text before joining. 

[KirstenKa]

@AronC  I have e.g 002 and 0001 I assume it should be 2 different values. For ecample in this code how can I "cast the fields as text before joining"

Outer Join( C8 )

SAP:

LOAD

   "Item ID" & '|' & Num( "Color" , '000' )               as %ItemColor_GlobalId,             

    "Item ID" & '|' & "Color"                        as ItemColor_GlobalId_SAP,

F 

[AronC]

Hi, in the case above the fields you generate are text. I don´t see the problem you have. Could you provide examples of the two tables SAP and PPT and some examples of resulting rows that you struggle with.

[KirstenKa]

 Hi @AronC  attached are the files. SAP and PPT are joined to C8. I don't know how the eitherway create integers or text in the code. Isn't "num" in the code already creating an integer? In the word file you see how it comes out in qlik

[AronC]

Num is creating a dual with both a textstring and a number. The textrepresentation as one part '001' for exampel, but the number would be 1.
If you would like it to be and integer, use num# instead which takes the text '001' and creates a number. To make sure the number is an integer, use floor(num#()) or sometimes only floor() is enough. I looked in your files but unfortunatly I still dont get what you want to achieve and what's not working out for you. 😞 

Always when I want to make a comparison or a key-field I never use duals because its hard to know when they are treated as text and when they are treated as a numeric value. If the problem is that your key fields are treated as number instead of text, I would try to force the value to be text as text(num(Field,'000')) and if you want to make sure they are integers  then floor(num#(field))

[KirstenKa]

Ah I see I tried both num and floor but they did not give the result needed. What is not working for me is that my color codes are not similar in Qlik and therefore my join is not working, but they are similar in my original datasets. I suspect it is something else. I will consult my colleague maybe he knows. Thanks for thinking with me!