Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
KirstenKa
Creator II
Creator II

Combine 000 and 0000 inclusion in one join

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,

              *

Labels (1)
6 Replies
AronC
Partner - Creator II
Partner - Creator II

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
Creator II
Creator II
Author

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

AronC
Partner - Creator II
Partner - Creator II

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
Creator II
Creator II
Author

 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
Partner - Creator II
Partner - Creator II

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
Creator II
Creator II
Author

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!

 

KirstenKa_0-1695214840783.png