Filtering of a custom dimension

[Defo]

Hi all,

I have an automation where I want to filter a Field using the "Select Field Values by Query". My Field is stored as string (but it is actually numeric) and so I can not apply a simple es. "<100".
There is a way to use a function to force the Field to a number? Like num# available in front end?
Any workaround to filter these kind of fields is welcome.

Modifying it in the Load Editor is not an approach I can follow.

Thanks,
Davide

[theoat]

You can try the function num().

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

[Defo]

Hi Théo!
Where should I put the function? In the green section (image below), like: num(MyField). Cause in this way it is not working. If I select the Field from the lookup I am not able to apply functions and if I write directly the formula num(MyField) in the input box I get an error.

Am I missing something?

Thanks,
Davide

[theoat]

I didn't understand it was here, it's not recommended to have field names in numeric format.

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

[Defo]

Probably I have not given enough info, my bad sorry.

I will try to add some details:
My field name is not numeric, it is "AccountID". It contains Customer IDs (like: 159122, 1769288, 187100) which are stored as string in my Qlik app. Due to their format the filter "<2276906" in the "Select Field Values by Query" is not working. I want to force QAA to consider the Account IDs as numbers.
In the front end of my app I have achieved this by adding a dimension to my table defined as "num#(AccountID)". Doing so I am able to filter this column in the chart by applying the criteria "<2276906".

I hope to have added some valuable info to my issue.

Thanks a lot Théo for your responses till now 🙂 

[andrefpc]

Hi @Defo ,

Were you able to solve this issue?

I'm struggling with a similar one, I've tried different blocks types but can't get it to work.

I can apply filter to other string fields but not the dynamic filter, like greater than.

[Defo]

Hi @andrefpc,

Unfortunately not. I'll continue to keep this thread open in order to let someone solve it in the future, hopefully.

🙂

[salmankojar]

Hi @Defo and @andrefpc you can achieve this by creating a master dimension via Automation and filter it in automation as per your requirement.

For example i have created a dummy app with the below script:

Test:

LOAD
RecNo() as row_no,
TEXT(RecNo()) as txt_rw_no
AutoGenerate 100
;

Exit script;

As you can see row_no is in number format but txt_rw_no is in text format and i will apply filter on it as you want.i created a straight table in app with these two dimensions and as you said i couldn't >= or <= filter in txt_rw_no field.

Next i created an automation which creates a master dimension named as Num_row_no by using the txt_rw_no field and converted it to Numeric format by NUM# function and saved it.
Then i applied filter on that master dimension by using "Select Field Values by Query" and the filters were applied successfully as desired on the app.
I have the attached the Automation json for your reference.if you have any query testing it then please ask i am happy to help,