change data value with conditions at load script (if match function)

[coloful_architect]

Hi guys,

I have a table like this , wanna edit the flag field.

without touching SKU, I want to change Flag on product group A, B , C with package type of bag from Yes to No

and also the flag on product group of C with package type of carton from Yes to No. 

Tried to make if/match with "and" /"or"...but must have missed something....it wont return what I expect

if (Match([Product Group], 'A','B','C') or Match([Package Type],'BAG')

and ((Match ([Product Group], 'C') or Match ([Package Type]),'Carton')) , 'No', [Flag])as "Flag",

and idea?

Product SKU	Product Group	sales	Package Type	Flag
1	A	935	box	Yes
2	A	134	bag	No
3	B	666	carton	Yes
4	B	915	bag	No
5	C	530	box	Yes
6	C	165	bag	No
7	D	542	box	Yes
8	D	660	carton	Yes
9	E	166	bag	Yes
10	E	900	box	Yes
11	F	305	carton	Yes
12	F	526	bag	Yes
13	G	700	carton	Yes
14	G	379	box	Yes
15	H	749	bag	Yes
16	H	734	carton	Yes
17	I	590	box	Yes
18	I	594	box	Yes

[pmaland]

Match is case sensitive, try using mixmatch and see if that works.

[coloful_architect]

well, tried. but still do not get preferred result. 

[pmaland]

What about adding a calculated field?  That way you can see a preview as you work on the formula.

[NageshSG]

Hi

Ideally a mapping table with the business rule you have specified should work. You can then apply the same when loading the data table. Especially useful if data is very large.

Few things i noticed:

1. I assume the sample table above is the source data with the 'Flag' field already in place but you want to change the value based on the condition:

1. 1 I want to change Flag on product group A, B , C with package type of bag from Yes to No (Your sample data already shows the values as 'No'!)

1.2  and also the flag on product group of C with package type of carton from Yes to No.  (Your sample data has no combination Product C and package type as 'carton' !)

Anyhow, can you try the following script with your full data set. 

Sample script below:

FilterNewFlag:
// first flag
LOAD
FlagKey,
'No' as Flag;
Load distinct

[Product SKU]&[Product Group]&[Package Type] as FlagKey
FROM
[sample data.xlsx]
(ooxml, embedded labels, table is FlagUpdate)
where
(
(([Product Group] = 'A' or [Product Group] = 'B' or [Product Group] = 'C'))
and
lower([Package Type]) = 'bag'
)

;
// this second set concatenates to above
LOAD
FlagKey,
'No' as Flag;
Load distinct
[Product SKU]&[Product Group]&[Package Type] as FlagKey
FROM
[sample data.xlsx]
(ooxml, embedded labels, table is FlagUpdate)
where
[Product Group] = 'C' and lower([Package Type]) = 'carton'
;

mapNewFlag:
Mapping load
FlagKey,
Flag
Resident FilterNewFlag ;
DROP Table FilterNewFlag;

Data:
LOAD [Product SKU],
[Product Group],
sales,
[Package Type],
Flag,
ApplyMap('mapNewFlag',[Product SKU]&[Product Group]&[Package Type],Flag) as NewFlag
FROM
[sample data.xlsx]
(ooxml, embedded labels, table is FlagUpdate);