Flag Rows Where Any of Same ID Has Negative Value

[QlikBusinessUser]

Hi,

I have a table with a subscription ID, quote ID and price. Subscription is unique but quote is not. I need to flag any subscription rows where any (even just 1) of the rows has a negative price to then drive a measure.

Simplified version of table:

Subscription ID, Quote ID, Price

1, 10, 2

2, 10, 4

3, 10, -4

4, 5, 1,

5, 5, 10

Required output:

Subscription ID, Quote ID, Price, Flag

1, 10, 2, Yes

2, 10, 4, Yes

3, 10, -4, Yes

4, 5, 1, No

5, 5, 10, No

[theoat · Accepted solution]

I  your script, add this :

left join(SimplifiedTable)
Load
[Quote ID],
if(min(Price)<0,'Oui','Non') as flagprice
RESIDENT SimplifiedTable
Group by [Quote ID];

I suceed with this :


Kind regards,
Théo ATRAGIE.

[Chanty4u]

You can create calculated dimension

=If(Sum({<Price={"<0"}>} Price) < 0, 'Yes', 'No')

[theoat]

This isn't recommended to use calculated dimension. This weighs down the visualisation.Don't get into this bad habit.

Kind regards,
Théo ATRAGIE.

[Chanty4u]

@theoat  i too agree. but sometime users/clients doesn't allow us to change any script.

if they need the solution in UI  this is usefull.

best

chanty 4u.