I know this has been discussed multiple times, but I was wondering whether anything has changed in recent releases. Is there any way to force a Qlik set expression to evaluate row by row for a field-to-field comparison? I need to write a complex expression that compares multiple fields within a single record, and I have run into the known issue (or feature, by design 🙂 ) that set expressions evaluate across the full set of distinct values. Please see the image below. We resolved the issue by replacing part of the set expression with if statements. The comparison involves a number of fields, so I was wondering whether any new set modifiers are available that would force row-level comparison when comparing two fields within a record.
I think the main issue here is the difference between set analysis and row-level evaluation. Set analysis works on the resulting data set, so it isn’t really designed to compare two fields independently for every row.
If the comparison needs to happen at record level, an IF() expression or an Aggr() approach would probably be more suitable. Another option, especially if this logic is used in several places, would be to create a flag in the load script and keep the chart expression simpler.
That’s similar to how I’d approach product comparisons in a hardware store: first define the comparison at the item level, then aggregate the result. I work with Anwer Hardware, so that kind of row-by-row comparison is a familiar way of thinking about the problem.
If all of the component fields for the evaluation exist on a single row of a single table then you should always do your evaluation in the load script. This saves a lot of computation on the front end and can really simplify your app.
Even if all the component parts do not start in the same table it is often worth bringing them together with ApplyMap and join statements and sometimes RESIDENT and GROUP BY statements.
