topic Re: Escape dollar sign extension in set analyis in App Development

Escape dollar sign extension in set analyis

christopherkramer — Tue, 28 Feb 2023 11:16:07 GMT

Assume I have this loadscript:

Load * Inline [ USERNAME,ID foo,1 bar,2 foo$(bar,3 ];

I now want a table that only lists the rows with USERNAME 'foo' and 'foo$(bar'.

I cannot use filters as this would filter the data globally, so I use set analysis in the dimension like this:

=Aggr(Only({<[USERNAME]={'foo'}>}USERNAME),USERNAME)

That works for 'foo'. But this one does not work:

=Aggr(Only({<[USERNAME]={'foo',' foo$(bar'}>}USERNAME),USERNAME)

This is because $( triggers dollar sign extension. I have tried a couple of things to escape this but nothing works. Any ideas?

Re: Escape dollar sign extension in set analyis

Or — Tue, 28 Feb 2023 11:23:23 GMT

https://community.qlik.com/t5/App-Development/Escape-Dollar-Sign-Expansion/td-p/1627896

Nasty, but it's the only option I know of that works.

Re: Escape dollar sign extension in set analyis

marcus_sommer — Tue, 28 Feb 2023 12:04:50 GMT

Like already hinted there is no escape/masking logic available and most common are the mentioned replace-approaches - whereby they might be made directly within the data. This means not loading ...$(... and replacing it within UI expressions else replacing it in the data and calling it appropriate within the UI. IMO it should be simpler.

Beside this you may load this information as loosen table and then selecting + locking the wanted values and the set analysis relates to it per p() like:

Aggr(Only({<[USERNAME] = p([USERNAMExyz])>}USERNAME),USERNAME)

Re: Escape dollar sign extension in set analyis

christopherkramer — Tue, 28 Feb 2023 12:33:07 GMT

Thanks @marcus_sommer and @Or for your replies.

If I replace $( loading the data, the replaced value will show up in filters.

Only way I can think of is to keep the original value in another field, configure the filter on this field, and use the field with the replaced value in the set analysis.

Nasty, indeed. I guess I will open an idea thread for this.

Re: Escape dollar sign extension in set analyis

marcus_sommer — Tue, 28 Feb 2023 12:46:55 GMT

This must not mandatory be a show-stopper because the suggestion was just to load this field twice - one time the origin one and the second time with adjustments - directly related or like hinted within a loosen table.

Loading a field more than once is not seldom else rather best practice to simplify the access or to extend a logic to features which would be much more complex by using other methods.

One examples for it are string-like date-fields as months 'Jan', 'Feb', ... which may cause various troubles by calculations/matching which are better made against 1, 2, ..., 12. Other cases may own total / average results within objects which couldn't be displayed if such dimension-values didn't exists.