Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MichaelR
Contributor
Contributor

QlikSense Concat Function

Hello Everyone!

I am attempting to set a calculation condition that will require users to select a 'DATE_TYPE' field if they select any of the date dimensions in a Canonical Calendar. An easy solution would be to simply use getSelectedCount on each of the fields that I want to check. However if I hard-code these into the calculation any time new date fields are added or names are changed, the calculation would also need to be updated. I'm trying to avoid leaving maintenance items like this in the applications where possible..

So my question is

1) Does anyone know of a good way to check for 1 or more selections on fields in certain table(s), and/or 

2) Can the solution below be modified to run getSelectedCount() on each field in certain table(s)

My best idea thus far is a powerful code snippet I stumbled across years ago that I have used extensively for similar problems. I understand what it does, but so far have struggled too much with HOW it works to modify it and use it for other purposes.

The function is:

[$(=Concat({1<$Table={'Table2','Table1'}>}distinct $Field,']=,[')&]=')

When stored in a variable and referenced within set analysis. This expands a pattern that makes all of the fields in those tables ignore selections for the calculation in which it is used. If stored in a variable vFieldFilters, the expanded results look like this if Table1 and Table2 contain Field1, Field2, Field3, and Field4:

Ex 1. - variable not expanded yet

sum({<$(vFieldFilters)>} totalAmt)

Ex 2. - after expansion

sum({<[Field1]=,[Field2]=,[Field3]=,[Field4]=>} totalAmt)

If this solution would be ideal, I would like to modify this function to create something like this for a calculation condition:

if(getSelectedCount(Field1)=0 or getSelectedCount(Field2)=0 or getSelectedCount(Field3)=0 or getSelectedCount(Field4)=0)

Can anyone help?

Labels (5)
1 Solution

Accepted Solutions
Nabeel_Asif
Employee
Employee

Hah! Couldn't resist having a go at this riddle.

The original expression you posted is making good use of the system fields.

To make the expression more understandable, I would remove the dollar expansion and just set it up to output a string. I used this as a measure in a text object:


'[' & Concat({1<$Table={'Table1','Table2'}>} distinct $Field,']=,[') &']='


Next change this to serve your needs:


'GetSelectedCount(' & Concat({1<$Table={'Table1','Table2'}>} distinct $Field,')=0 and GetSelectedCount(') &')=0'


You don't need an IF condition as once evaluated this expression will result in a Boolean value.

And finally you can use dollar sign expansion to evaluate this as an expression:


$(='GetSelectedCount(' & Concat({1<$Table={'Table1','Table2'}>} distinct $Field,')=0 and GetSelectedCount(') &')=0')


HOWEVER, this seems a pretty complicated calculation condition. Qlik Sense now has the 'Always one selected value' option for fields and the ability to set default bookmarks. Can this work in your case?

View solution in original post

2 Replies
Nabeel_Asif
Employee
Employee

Hah! Couldn't resist having a go at this riddle.

The original expression you posted is making good use of the system fields.

To make the expression more understandable, I would remove the dollar expansion and just set it up to output a string. I used this as a measure in a text object:


'[' & Concat({1<$Table={'Table1','Table2'}>} distinct $Field,']=,[') &']='


Next change this to serve your needs:


'GetSelectedCount(' & Concat({1<$Table={'Table1','Table2'}>} distinct $Field,')=0 and GetSelectedCount(') &')=0'


You don't need an IF condition as once evaluated this expression will result in a Boolean value.

And finally you can use dollar sign expansion to evaluate this as an expression:


$(='GetSelectedCount(' & Concat({1<$Table={'Table1','Table2'}>} distinct $Field,')=0 and GetSelectedCount(') &')=0')


HOWEVER, this seems a pretty complicated calculation condition. Qlik Sense now has the 'Always one selected value' option for fields and the ability to set default bookmarks. Can this work in your case?

MichaelR
Contributor
Contributor
Author

Thank you Nabeel! The required selection will be perfect for my use case, I wasn't aware of that option.
On the concat function to build a string of all fields in the specified tables, the changes you suggested worked like a charm after a few modifications!


GetSelectedCount($(=Concat({1<$Table={'Table1','Table2'}>} distinct $Field,')=0 and GetSelectedCount(') &'))=0


Results:


GetSelectedCount(field1)=0 and GetSelectedCount(field2)=0 and GetSelectedCount(field3)=0 and GetSelectedCount(field4)=0