Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
skompel2
New Contributor II

Set Analysis within If statement-multiple conditions

I've the following data:

LocationPartQty
CDEngine1
AE00Transmission3
TR00Pump1
TR02Transmission1
TR03Transmission1
TR04Pump1
SDPump1
CDEngine3
AE00Transmission1
TR04Pump1

 

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:

LocationPartQty
newengine4
newtransmission3
semitransmission4
semipump1
finaltransmission2
finalpump2
incompletepump1

 

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?


 

3 Replies
jwjackso
Valued Contributor

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')))

 

Employee
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]:
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
New 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?