How should I express "not equal to" operator in Set Analysis in Qlik Sense?

[srihitha]

Hi,

Can someone pls help me with this?

[Product Group] , [Product Line] are two fields in the same table

I want Sum(sales) for all field values where [Product Group] is not equal to [Product Line]

Sum({<[Product Group] not equal to [Product Line]>}Sales)

How should I express this "not equal to" in set analysis

[marcus_sommer · Accepted solution]

It's not possible with set analysis but you could do this:

if([Product Group] <> [Product Line], Sum(Sales))

- Marcus

[vchuprina · Accepted solution]

Hi, 

Try following expression:

Sum({<ID = {"=([Product Group]<>[Product Line])" } >} Sales)

My data:

Data:
LOAD * Inline [
 ID, Product Group, Product Line, Sales
 1,1, 1, 5
 2,1, 2, 10,
 3,2, 2, 20,
 4, 2, 1, 7
 ];

Result:

Regards,

Vitalii

[vchuprina]

Please note that you can use any operations for numerical fields: =, <>, >=, <=, >, <.

And = <> for text fields

Regards,

Vitalii

[marcus_sommer]

It's possible to write it in a set analysis syntax but it's not a real set analysis because:

{"=([Product Group]<>[Product Line])" }

forced a row-level evaluation like an if-loop. The real advantage of a set analysis is lost.

To implement a real set analysis the essential work must be done within the script, maybe with:

-([Product Group]<>[Product Line]) as Flag

and then the flag-values could be used as set analysis condition and/or as multiplier and/or as selection/dimension in the UI.

- Marcus

[vchuprina · Accepted solution]

Marcus,

I agree that creating a flag is the best option in the current case because both fields are in one table and it's easy to do. But if fields are in the different tables and it's not possible to create a flag in the script in this case it makes sense to use such sets as {"=([Product Group]<>[Product Line])" }.

I believe from a performance point of view it's better than simple if:

if([Product Group] <> [Product Line], Sum(Sales))

Also, I'm not sure that in small apps you will fill any difference between following expression

if([Product Group] <> [Product Line], Sum(Sales))

Sum({<ID = {"=([Product Group]<>[Product Line])" } >} Sales)

Sum({<Flag = {1} >} Sales)

Regards,

Vitalii

[marcus_sommer]

Yes, in rather small applications it won't make bigger differences. Unfortunately I didn't made any testing on the performance impact between the various possibilities yet but I'm sure that there is some impact with bigger data-sets and more complex data-models.

- Marcus

[srihitha]

Hi Vitalii,

Thanks for the solution. Now, I want to find sum(sales)  group by id, Product Group, Product Line when "Product Group" is equal to "Product Line". How can I add below part of script in comments to the complete script.

I tried doing this using preceding load and resident load ,but it is not working

/*load *,
if([Product Group] = [Product Line])
then
Sum(Sales) group by id,Product Group,Product Line
end if; */

//Complete Script:

Data:
LOAD * Inline [
ID, Product Group, Product Line, Sales
1,Cat, Cat, 5
1,Cat, Cat,10
2,Cat, Dog, 10
3,Cat, Cat, 20
4,Dog, Cat, 7
];

[vchuprina · Accepted solution]

Hi, 

Try following: 

Data:
LOAD
     ID,
     [Product Group],
     [Product Line],
    if([Product Group] = [Product Line], Sum(Sales)) as Sales
group by
     ID,
     [Product Group],
     [Product Line]
;
LOAD * Inline [
ID, Product Group, Product Line, Sales
1,Cat, Cat, 5
1,Cat, Cat,10
2,Cat, Dog, 10
3,Cat, Cat, 20
4,Dog, Cat, 7
];

Result:

Also, you can remove 'IF' from sales calculation and move the condition to the 'Where' statement in this case only rows where [Product Group] = [Product Line] will be in your table

Data:
LOAD
     ID,
     [Product Group],
     [Product Line],
    Sum(Sales) as Sales
Where [Product Group] = [Product Line]
group by
     ID,
     [Product Group],
     [Product Line]
;
LOAD * Inline [
ID, Product Group, Product Line, Sales
1,Cat, Cat, 5
1,Cat, Cat,10
2,Cat, Dog, 10
3,Cat, Cat, 20
4,Dog, Cat, 7
];

Result: