Show multiple values with a condition

[Dante_83]

Hi All,

I have two columns labeled Maint Cost and PM Req. Under the PM Req column, I have 'No Maint Req' and 'Maint Req'. I want to create another column that shows 'No Maint Req' cost above $300 (Exclude cost under $300) and Maint Req to show all regardless of cost only.

Below is my expression.

if([PM Req]='No Maint Req',Sum[Maint Cost]>'$300,Sum[Maint Cost])

I got every 'No Maint Req" that is below $300 to show $0. But, 'No Maint Req' above $300, I got a -$1. May someone help? I really appreciate any help you can provide.

[sidhiq91 · Accepted solution]

@Dante_83  Please see the below script. Let me know if this has resolved your issue.

NoConcatenate
Temp:
Load * inline [
MaintenanceCost, PM Req
200,No Maint Req
300,No Maint Req
400,Maint Req
600,Maint Req
800,No Maint Req
900,Maint Req
];

Temp1:
Load *,
if([PM Req]='No Maint Req' and MaintenanceCost>300,MaintenanceCost) as New_Column
Resident Temp
Where [PM Req]='No Maint Req' and MaintenanceCost>300;
Concatenate
Load *,
if([PM Req]='Maint Req' ,MaintenanceCost) as New_Column
Resident Temp
where [PM Req]='Maint Req';
Drop table Temp;

Exit Script;

If yes, please like and accept it as a solution.

[MendyS]

hi

try this - 

"

if([PM Req]='No Maint Req',
If([Maint Cost]>'$300,
'Over 300 and No Maint Req',
'Over 300 and No Maint Req'),
'Maint Req')

"

[Dante_83]

Hello sidhiq91,

Thank you for helping me. I'm still green in Qlik Sense and generating data in the script is something that I'm working on. But, do I have to generate a script to show all value greater than $300? 

[Dante_83]

Hello MendyS,

Thank you for your help! But, do you have the expression to show the Value greater $300 for No Maint Req instead of 'No Maint Req'?

[sidhiq91]

@Dante_83  This Script that I had mentioned above resolves your issue. Please give it a try and let me know in case if you are facing any issues.

[Dante_83]

Hello sidhiq91,

I've use the data script you provided and it works great to identify the label. But, I'm having trouble viewing the 'Maint Cost' value for PM Req 'Maint Req' and ('No Maint Req' is greater than $300).

For example:

If the 'Pm Req' has a 'Maint Req', I want to see all 'Maint Cost' values associated with 'Maint Req'.

If the 'Pm Req' has a 'No Maint Req', I want to see all 'Maint Cost' that is greater than $300 associated with 'No Maint Req'.

I've been trying to get this right and I have no success. Please help? Thank you in advance.