If condition

[Bharathi09]

Hi All,

I have two columns in my dataset 'Code' and 'Parts'

I have to create Country column in the backend scripting by writing below logic.

if any of the code = 34 or 35 country = Canada 

if any of the code =14, 20 OR 43 country = Mexico

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

Please help me with this

Thanks in advance

[RsQK]

Hi, if by "if any of the code = 34 or 35 country = Canada" you meant that code = 34 or parts = 35, then something like this should do the trick:

[ml_all_countries]:
MAPPING LOAD
CODE & '|' & PARTS,
'Both' as COUNTRY
INLINE [
CODE,PARTS
34,20
34,43
35,20
35,43
14,20
14,43
];

[ml_can_code]:
MAPPING LOAD
CODE,
'Canada' as COUNTRY
INLINE [
CODE,COUNTRY
34
];

[ml_can_parts]:
MAPPING LOAD
PARTS,
'Canada' as COUNTRY
INLINE [
PARTS,COUNTRY
35
];

[ml_mex_code]:
MAPPING LOAD
CODE,
'Mexico' as COUNTRY
INLINE [
CODE,COUNTRY
14
20
];

[ml_mex_parts]:
MAPPING LOAD
PARTS,
'Mexico' as COUNTRY
INLINE [
PARTS,COUNTRY
43
];


data:
LOAD *,
ROWNO() AS row,
APPLYMAP('ml_all_countries',CODE & '|' & PARTS,
APPLYMAP('ml_can_code',CODE,
APPLYMAP('ml_can_parts',PARTS,
APPLYMAP('ml_mex_code',CODE,
APPLYMAP('ml_mex_parts',PARTS,'Not defined'))))) as COUNTRY
INLINE [
CODE,PARTS
34,20
34,43
35,20
35,43
14,20
14,43
34,100
35,100
35,35
14,100
100,43
20,100
];

[Bharathi09]

I have code column in excel as below
[cid:100e706f-fa4d-4fab-bcc7-46afa0060c57]


I mean to tell if code=
if any of the code = 34 or 35 country = Canada
if any of the code =14, 20 OR 43 country = Mexico
if any of the code = 34 or 35 and 14, 20 OR 43 country = Both

I need to create as new Country column in backend for above requirement

Please help

Thanks in advance

[RsQK]

If you could provide a few codes with desired results, that'd be great.

[Bharathi09]

Sorry I don't have any, actually I'm still in learning stage

if you could please provide the solution for my query, it would be great.


Thanks

[RsQK]

You wrote, that you have a column in excel with codes like these:
[cid:100e706f-fa4d-4fab-bcc7-46afa0060c57]

If you could provide more codes like these with countries that should be calculated for them in a form like this:

Code	Country
[cid:100e706f-fa4d-4fab-bcc7-46afa0060c57]	???
[cid:200a556m-ab4e-4fab-bcc7-34atg1234s51]	???
...	???

[Bharathi09]

Code means column name code

This is my excel and the from this I need to create country column in scripting by using below logic

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

Please help

thanks

[RsQK]

Based on your excel file, should the result look like this?

[Bharathi09]

can you please send me the code you have written?

[RsQK]

Sure:

SET p_can_codes = 34,35;
SET p_mex_codes = 14,20,43;

temp_data:
LOAD *,
ROWNO() AS Row
INLINE [
Part,Code
1,34
2,35
3,14
4,20
5,43
6,34
7,35
8,14
9,20
10,43
1,14
2,20
3,34
4,35
5,14
6,43
];

//Canada:
temp_can:
LOAD DISTINCT
Part,
Code	AS Can_code
RESIDENT temp_data;

INNER JOIN (temp_can)
LOAD
SUBFIELD('$(p_can_codes)',',') AS Can_code
AUTOGENERATE 1;

temp_can2:
LOAD DISTINCT
Part
RESIDENT temp_can;

DROP TABLE temp_can;

LEFT JOIN (temp_data)
LOAD
Part,
1	AS can_flag
RESIDENT temp_can2;

DROP TABLE temp_can2;

//Mexico:
temp_mex:
LOAD DISTINCT
Part,
Code	AS Mex_code
RESIDENT temp_data;

INNER JOIN (temp_mex)
LOAD
SUBFIELD('$(p_mex_codes)',',') AS Mex_code
AUTOGENERATE 1;

temp_mex2:
LOAD DISTINCT
Part
RESIDENT temp_mex;

DROP TABLE temp_mex;

LEFT JOIN (temp_data)
LOAD
Part,
1	AS mex_flag
RESIDENT temp_mex2;

DROP TABLE temp_mex2;

data:
LOAD *,
PICK(MATCH(comb_flag,'1|1','|1','1|'),'Both','Mexico','Canada') AS Country
;
LOAD *,
can_flag & '|' & mex_flag as comb_flag
RESIDENT temp_data;

DROP TABLE temp_data;

DROP FIELDS can_flag,mex_flag,comb_flag;