QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for
Did you mean:
Highlighted
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)

3 Replies
Highlighted
Specialist

Re: Set Analysis within If statement-multiple conditions

Try this:

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

Highlighted
Employee

Re: Set Analysis within If statement-multiple conditions

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 ',');
Highlighted
Contributor II

Re: Set Analysis within If statement-multiple conditions

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?