Set Analysis within If statement-multiple conditions

[skompel2]

I've the following data:

Location	Part	Qty
CD	Engine	1
AE00	Transmission	3
TR00	Pump	1
TR02	Transmission	1
TR03	Transmission	1
TR04	Pump	1
SD	Pump	1
CD	Engine	3
AE00	Transmission	1
TR04	Pump	1

I want to group and rename all parts at locations "CD" as new, location "TR00" as incomplete, parts in location "AE00" and "SD" as semi and "TR02,TR03,TR04" as final. Then sum the quantity using expression " =Sum(Qty)". My data should look like this ideally:

Location	Part	Qty
new	engine	4
new	transmission	3
semi	transmission	4
semi	pump	1
final	transmission	2
final	pump	2
incomplete	pump	1

I made a "calculated dimension" using If statement with AND condition like this but It says error in expression:

=If([Proposed Location]='CD', 'new') and If([Proposed Location]=TR00', 'incomplete') and If({<[Proposed Location]={"TR02","TR03","TR04"}>}, 'Final')  and If({<[Proposed Location]={"AE00","SD"}>}, 'Final')

How do I solve this?

[jwjackso]

Try this:

=If([Proposed Location]='CD','new',
If([Proposed Location] = 'TR00','incomplete',
If(Match([Proposed Location],'TR02','TR03','TR04','AE00','SD') > 0,'Final')))

[Lisa_P]

The best way to solve this is to use a mapping table in the script to do the conversion of the locations or create a new field.

It is possible to do as a calculated dimension, but this may impact performance on the front end.

See sample script:
[LocationMapping]:
MAPPING LOAD * INLINE
[
LocationMapping-FROM,LocationMapping-TO
TR00,incomplete
CD,new
SD,semi
AE00,semi
TR04,final
TR03,final
TR02,final
];

[Table]:
LOAD
APPLYMAP( 'LocationMapping', [Location]) AS [Location],
[Part],
[Qty];
LOAD * INLINE
[
Location,Part,Qty
CD,Engine,1
AE00,Transmission,3
TR00,Pump,1
TR02,Transmission,1
TR03,Transmission,1
TR04,Pump,1
SD,Pump,1
CD,Engine,3
AE00,Transmission,1
TR04,Pump,1
](delimiter is ',');

[skompel2]

Lisa,

can you give a little more details on the script. My data is in an excel file and when i open script it has all the LOAD statements by default.

Should i write the MAPPING table code at the end of the script? And is "[TABLE]" creating a new pivot table? How will the code calculate sum(Qty) as asked in the question?