Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Set Analysis within If statement-multiple conditions

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?

Labels (6)

• ### Set Analysis

3 Replies
Specialist III

Try this:

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

Employee
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]:
[
LocationMapping-FROM,LocationMapping-TO
TR00,incomplete
CD,new
SD,semi
AE00,semi
TR04,final
TR03,final
TR02,final
];

[Table]:
APPLYMAP( 'LocationMapping', [Location]) AS [Location],
[Part],
[Qty];
[
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 ',');
Contributor II
Author

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?

Community Browser