Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
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
Highlighted
Specialist II
Specialist II

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

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?