if statements, and , or statements in scripting

[Bharathi09]

Hi All,

I have excel sheet with 2 columns as :

I have requirement like:

if  code = 34 or 35 country = Canada 

if code =14, 20 OR 43 country = Mexico

if  code = 34 or 35 and 14, 20 OR 43 country = Both

It should be created as new column in backend scripting with name 'country'

I wrote code like this in my application

LOAD

    Part,

    Code,

    If(Code=34 or 35,'Canada')

    ,If(Code=(14 or 23) and (20 or 43),'Mexico'),

    If(Code=(34 or 35) and (14 or 23) and (20 or 43),'Both' ) as Country

FROM [lib://Countrytest/MpTest.xlsx]

(ooxml, embedded labels, table is Sheet1)

But it's only showing Canada,

The output should look like below

Please help

Thanks in advance

[rubenmarin]

Hi, that syntax for the 'if' is not correct, it should be:  If(Code=34 or Code=35,'Canada')

You can also use Match() to define multiple values to compare: If(Match(Code,34,35),'Canada')

Also, note that each if will only have it's row data, so none of them will accomplish the 'and' as each row will only have one value for Code.

First you will need to group the values to have all codes of each part, then check all the codes in the same row, and merge with the initial table:

DataTable:
LOAD
    Part,
    Code
FROM [lib://Countrytest/MpTest.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join (DataTable)
LOAD Part,
  If(WildMatch(Codes,'*;34;*','*;35;*'), 'Canada',  
  If(WildMatch(Codes,'*;14;*','*;23;*') and WildMatch(Codes,'*;20;*','*;43;*'), 'Mexico',
  If(WildMatch(Codes,'*;34;*','*;35;*') and WildMatch(Codes,'*;14;*','*;23;*') and WildMatch(Codes,'*;20;*','*;43;*'), 'Both'))) as Country
;
LOAD Part, 
  ';' & Concat(distinct Code,';') & ';' as Codes
Resident DataTable Group by Part;

[Bharathi09]

Hi,
[cid:b2da4914-26ac-4a7e-a4a1-8126e3bf0a48]

It's showing output like this, it's not working

Please help me out

Thanks in advance

[rubenmarin]

Hi, I can't see the output. 

In my last answer I focus on the syntax, but the logic is incorrect, it should ask first for 'both'. Also I'm not sure of the Mexico conditions, now that I see it, in your first requirement says "if code =14, 20 OR 43 country = Mexico", that's differnt from what is written below: ",If(Code=(14 or 23) and (20 or 43),'Mexico')," Where that 23 comes from and why the and?

With this, without an 'and to check for a single country I think it's better to make it in different steps:

DataTable:
LOAD
    Part,
    Code,
    If(Match(Code,34,35),1,0) as isCanada,
    If(Match(Code,14,20,43),1,0) as isMexico,
FROM [lib://Countrytest/MpTest.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join (DataTable)
LOAD Part, 
  If(Sum(isCanada) and Sum(isMexico) // both are different than 0
    ,'Both'
    ,If(Sum(isCanada)
      ,'Canada'
      ,If(Sum(isMexico) // Maybe this check is not needed
        ,'Mexico'
  ))) as Country
Resident DataTable Group by Part;

DROP Field isCanada;
DROP Field isMexico;

[Bharathi09]

Thanks a lot, it worked
but can you please explain me why we used sum(isCanada) in 2nd load

Thanks again

[rubenmarin]

Hi, 'If' returns false when the condition is 0, and True for any other value. So if none of the Part codes is from canada, the result will be 0, and the 'if' will be evaluated as false. It there is at least one row for the Part that have isCanada as 1, it will return at least 1 (can be 1, 2, 3...) and it will be evaluated as true.

"Sum(isCanada)" is the same as write "Sum(isCanada)<>0" or in this case "Sum(isCanada)>0", all options will do the same.