Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Vilius
Contributor III
Contributor III

Filtering by non-existent data type

I have such data set structure:

Requests

req_idtype_idreq_name
11Request Nr 1.
2 Request Nr. 2
3 Request Nr. 3
42Request Nr. 4

 

Request Types

type_idtype_name
1Cargo
2Passenger

 

Requests and Request Types are joined via Data Manager. Request Nr. 2 and Request Nr. 3  in table Requests doesn't have Request Type set, but it is known internally for our salesmen that all such requests should be considered Passenger requests.

 I want to create a Filter Pane which would allow me to filter Requests table. The Filter Pane itself is not an issue, but I cannot wrap my head around how to do filtering in the table. If I select "Cargo" it is filtering fine, but if I select "Passenger" I only get Request Nr. 4 row. I need to select rows 2, 3 and 4 somehow. Any ideas?

4 Replies
_ylc
Partner
Partner

You can update the type_id filed in your Requests table to link it properly to the request type.

if(len(type_id)=0, 2, type_id) as type_id

This line checks that if the type_id is null then it will autoset the value of type_id to 2.

Vilius
Contributor III
Contributor III
Author

Tables come from external database and the data model is done purely via Data Manager. Is there a way to replace the values without converting the model to scripted tables?

_ylc
Partner
Partner

Nope, but you can create a calculated field in the Data Manager.

Go to your Data Manager

_ylc_0-1592868287256.png

 

 

Click on the Requests Table and click the pen icon.

You will be taken to this screen, you should rename your type_id field to original_type_id field by double clicking the name.

 

_ylc_1-1592868318758.png

 

Click on the Add field then click the Calculated field

 

_ylc_2-1592868465164.png

 

 

 

fill up the details:

 

_ylc_0-1592868433172.png

 

then press Create.

Vilius
Contributor III
Contributor III
Author

I'm very sorry, but I've looked at the wrong tables. I cannot do value replacement, because my real data set model is as follows:

Requests

req_idreq_name
1Request Nr. 1
2Request Nr. 2
3Request Nr. 3
4Request Nr. 4

 

Request Types

type_idtype_name
1Cargo
2Passenger

 

RequestType Link Table

req_idtype_id
11
42

 

There can be Requests without links to type table, and some requests can have both types.