Qlik Community

Integration, Extension & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
beatYesterday
Contributor III
Contributor III

Accessing relationship between qElemNumber and actual field Value

Hello,

Very new to Qliksense and its engine API.  I'm ultimately trying to filter(select) a subset of rows based on the value contained within a specific column.  I have found the  https://help.qlik.com/en-US/sense-developer/June2020/apis/EngineAPI/services-GenericObject-SelectHyp... method, which allows you to filter base don qElemNumber and dimension array key.  This is fine, but there is significant guess work that would need to go into figuring out which qElemNumber is assigned to a specific field value.  For example, if 'US' was the field value, how do I know if it is assigned qElemNumber 1, 2, 3,4 or something else?

Is there a method that allows me to filter/select by the actual field value?

If not, how do people typically structure API calls for selections that allow you to filter/select based on the column values themselves?

Thank you!

 

1 Solution

Accepted Solutions
beatYesterday
Contributor III
Contributor III
Author

I have finally created the solution I was aiming for and am following back to pass what I learned to others that may be experiencing the same issue.  Again, I'm using python and executed the following JSON commands to the ENGINE API while using the websocket, ssl, datetime and json Python libraries.

@Damien_Villaret was correct that the SelectValues method is a way to select values in a field based on the actual human-visible value (i.e. not the ElemNumber).  

The steps needed to do this successfully involve connecting to the relevant app in Engine API.  Then you must select the field that you want to filter on.  This is accomplished using the JSON below

import websocket
import ssl
import json
import datetime
ws = websocket.create_connection(url, sslopt = certs, header = wsHeaders)

ws.send(json.dumps({
    "handle": 1, #//always handle 1 since this is an app-level method
    "method": "GetField",
    "params": {
        "qFieldName": "QLIK_FIELD_NAME_HERE",#//field name in App to filter on
        "qStateName": ""
    }
}))

 

This will return the object.  Next you will need to send the SelectValues method with attributes needed.

dicts =  {"qText": "Company A", "qIsNumeric": False, "qNumber": 0},{"qText": "Company B","qIsNumeric": False,"qNumber": 0}

ws.send(json.dumps(
    {
    "handle": HANDLE_RETURNED_FROM_GetField,
    "method": "SelectValues",
    "params": {
        "qFieldValues":dicts,
        "qToggleMode": False,
        "qSoftLock": False
    }
}
    ))

 

This will work for fields that are strings.  The above will not work for numeric or date fields.  To select values for these fields the qIsNumberic must be set to True, and the qNumber is where you send the numeric value to be selected.  For example, if I wanted to select the values 1 and 2 from a field containing the numbers 1-10, I would use the following:

#filtering for numeric values
dicts =  {"qText": "", "qIsNumeric": True, "qNumber": 1},{"qText": "","qIsNumeric": True,"qNumber": 2}

 

Date filters are selected the same way that numeric fields are, since Qlik stores these as numbers (in the same way that Excel does).  In order to go from a date string to the same numeric value I use the code below

time_diff = (datetime.datetime.strptime(string_date, '%Y-%m-%d') - datetime.datetime.strptime('1899-12-30', '%Y-%m-%d'))

#convert time diff object to a float
date_num = time_diff / datetime.timedelta(days = 1)

dicts =  {"qText": "", "qIsNumeric": True, "qNumber": date_num }

 

View solution in original post

9 Replies
beatYesterday
Contributor III
Contributor III
Author

Hello,

I'm still stuck with this one and would appreciate any guidance from the community.  

Thank you

Damien_Villaret
Support
Support

Hello,

This method was created for building visualizations - where the data will be represented by clickable labels (data fetch with getLayout for example)
This is not optimal for your use case.

You will be better off with SelectListObjectValues https://qlik.dev/apis/json-rpc/qix/genericobject#%23%2Fentries%2FGenericObject%2Fentries%2FSelectLis...

so: create a list (instead of a cube) => do selections with “values”

If the issue is solved please mark the answer with Accept as Solution.
beatYesterday
Contributor III
Contributor III
Author

Thank you, @Damien_Villaret .    Conceptually, I understand what you're telling me, that the hypercube object is not the appropriate way to access/filter the data, and that a list object is, since I can use that SelectListObjectValues function.  Dumb follow-up question, how does one create a list object?  Is this something that I can transform from they hypercube object, or is this something I need to set in an app's sheet or visualization so that the API recognizes it as a list object type?  I've tried reading the documents but I still haven't figured out how to define the list object and whether that can be done from data in a hyper cube.

 

Damien_Villaret
Support
Support

You can read a bit more about qListObject at this link:
https://help.qlik.com/en-US/sense-developer/November2020/Subsystems/Extensions/Content/Sense_Extensi...

 

You can create a list object:

- with the capability API:  https://help.qlik.com/en-US/sense-developer/November2020/Subsystems/APIs/Content/Sense_ClientAPIs/Ca...

- or with the Engine API (CreateObject or CreateSessionObject method and specify qType:"listObject")

If the issue is solved please mark the answer with Accept as Solution.
beatYesterday
Contributor III
Contributor III
Author

Thanks, @Damien_Villaret , I'm still not getting it.  I've read the documents and can create a list object from scratch using the examples shown here, https://help.qlik.com/en-US/sense-developer/November2020/Subsystems/EngineAPI/Content/Sense_EngineAP... . However, I have not yet found a way to load the list object with data from my existing app.  Is there any guidance or tutorial that you could point me to towards this end?  Ideally, I'd be able to create a list object from either a Hypercube or table object.  Is that possible?

 

Additionally, can you confirm that the SelectListObjectValues actually uses the visible values in Qlik and not the element numbers?  The documentation says that it uses element numbers, not the values themselves.  So, even if I did get this to work I'm not sure I'd be able to filter by the actual value in a field that a user sees.  Is there a means to get the mapping between element number and actual value of a field?  Any guidance here would be very helpful.

Thank you again.

QuickDelivery12
Contributor
Contributor

Glad you brought this up. Having a similar issue. 

QuickDelivery12
Contributor
Contributor

Glad you brought this up, having a similar issue.

aaz
Employee
Employee

@beatYesterday ,

Hi, maybe this tutorial can help http://opensrc.axisgroup.com/tutorials/engine/107.%20Creating%20Filters%20with%20ListObjects.html

The `SelectListObjectValues` uses element numbers, the method name is, unfortunately, a little bit confusing.
I suggest you use the Field API's `selectValues` for your use case https://qlik.dev/apis/json-rpc/qix/field#%23%2Fentries%2FField%2Fentries%2FSelectValues .

beatYesterday
Contributor III
Contributor III
Author

I have finally created the solution I was aiming for and am following back to pass what I learned to others that may be experiencing the same issue.  Again, I'm using python and executed the following JSON commands to the ENGINE API while using the websocket, ssl, datetime and json Python libraries.

@Damien_Villaret was correct that the SelectValues method is a way to select values in a field based on the actual human-visible value (i.e. not the ElemNumber).  

The steps needed to do this successfully involve connecting to the relevant app in Engine API.  Then you must select the field that you want to filter on.  This is accomplished using the JSON below

import websocket
import ssl
import json
import datetime
ws = websocket.create_connection(url, sslopt = certs, header = wsHeaders)

ws.send(json.dumps({
    "handle": 1, #//always handle 1 since this is an app-level method
    "method": "GetField",
    "params": {
        "qFieldName": "QLIK_FIELD_NAME_HERE",#//field name in App to filter on
        "qStateName": ""
    }
}))

 

This will return the object.  Next you will need to send the SelectValues method with attributes needed.

dicts =  {"qText": "Company A", "qIsNumeric": False, "qNumber": 0},{"qText": "Company B","qIsNumeric": False,"qNumber": 0}

ws.send(json.dumps(
    {
    "handle": HANDLE_RETURNED_FROM_GetField,
    "method": "SelectValues",
    "params": {
        "qFieldValues":dicts,
        "qToggleMode": False,
        "qSoftLock": False
    }
}
    ))

 

This will work for fields that are strings.  The above will not work for numeric or date fields.  To select values for these fields the qIsNumberic must be set to True, and the qNumber is where you send the numeric value to be selected.  For example, if I wanted to select the values 1 and 2 from a field containing the numbers 1-10, I would use the following:

#filtering for numeric values
dicts =  {"qText": "", "qIsNumeric": True, "qNumber": 1},{"qText": "","qIsNumeric": True,"qNumber": 2}

 

Date filters are selected the same way that numeric fields are, since Qlik stores these as numbers (in the same way that Excel does).  In order to go from a date string to the same numeric value I use the code below

time_diff = (datetime.datetime.strptime(string_date, '%Y-%m-%d') - datetime.datetime.strptime('1899-12-30', '%Y-%m-%d'))

#convert time diff object to a float
date_num = time_diff / datetime.timedelta(days = 1)

dicts =  {"qText": "", "qIsNumeric": True, "qNumber": date_num }

 

View solution in original post