topic Re: If condition in App Development

If condition

Bharathi09 — Mon, 19 Sep 2022 08:27:55 GMT

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

Re: If condition

RsQK — Mon, 19 Sep 2022 08:41:37 GMT

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

Re: If condition

Bharathi09 — Mon, 19 Sep 2022 08:49:21 GMT

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

Re: If condition

RsQK — Mon, 19 Sep 2022 08:55:36 GMT

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

Re: If condition

Bharathi09 — Mon, 19 Sep 2022 09:42:21 GMT

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

Re: If condition

RsQK — Mon, 19 Sep 2022 10:09:54 GMT

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]	???
...	???

Re: If condition

Bharathi09 — Mon, 19 Sep 2022 10:14:35 GMT

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

Re: If condition

RsQK — Mon, 19 Sep 2022 10:33:10 GMT

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

Re: If condition

Bharathi09 — Mon, 19 Sep 2022 11:46:21 GMT

can you please send me the code you have written?

Re: If condition

RsQK — Mon, 19 Sep 2022 12:22:48 GMT

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;

Re: If condition

Bharathi09 — Mon, 19 Sep 2022 12:31:21 GMT

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

Re: If condition

RsQK — Mon, 19 Sep 2022 12:41:45 GMT

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;

Re: If condition

Bharathi09 — Mon, 19 Sep 2022 12:57:21 GMT

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