Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Match is case sensitive, try using mixmatch and see if that works.
well, tried. but still do not get preferred result.
What about adding a calculated field? That way you can see a preview as you work on the formula.
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);