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

[Bharathi09]

I already loaded data , inline is not required, I loaded part and code fields in application

can you send code with normal load

Thanks alot

[RsQK · Accepted solution]

Since I dont know your data connection name, file name or sheet name, you will have to adjust the FROM part. Just change the "YOUR_CONNECTION_NAME", "YOUR_FILE_NAME", "YOUR_SHEET_NAME" accordingly.

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

temp_data:
LOAD 
Part,
Code
FROM [lib://YOUR_CONNECTION_NAME/YOUR_FILE_NAME.xlsx]
(ooxml, embedded labels, table is 'YOUR_SHEET_NAME');

//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;

[Bharathi09]

Thanks alot
This helped me
But can we minimize the code?