Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filtering data using one checkbox

I have a big project with a lot of table. I have done an example to simply what I really want to obtain. Below is attached.

Suppose I have two tables, "Job_Data" and "Personal_Data" which are related through a filed called "ID"

Both tables have their own data. Both tables have a bit column (0 or 1 possible values). For "Job_Data" table is "Consultant" which indicates if the person is a consultant, and for "Personal_Data" table is "license_car" to indicate if that person can drive a car.

So, according this model, I am trying to filter the data from both tables using one checkbox.

This checkbox will filter data by taken into account below posible cases:

Consultantlicense_car
00
01
10
11

So I have created one checkbox "Only consultants and/or license car".

So according to the sample attached:

  • If I check this checkbox:
    • For table "Job_Data" I want to show the registers with "ID" 300 and 900 because "Consultant" field is marked as 1
    • For table "Personal_Data" I want to only show registers with "ID" 120, 300 and 1000 because their "license_car" field is marked as 1. However I do not want to show "ID" 200 and 900 because in this case their "license_car" field is 0.
  • If I uncheck this checkbox:
    • I want to show all data for both tables independently of the values for "Consultant" and "license_car" fields.

So how can I do this?  Preferably, I would like to use an expression through the checkbox if possible and not script.

1 Solution

Accepted Solutions
vinieme12
Champion II
Champion II

Then let the COMMON Table behave like the link table

[Job_Data]:

LOAD *,ID as ID_JD INLINE [

ID, Name, Category, Consultant

1000, 'David', 'Auxiliar', 0

900, 'John', 'Analyst', 1

200, 'Anne', 'Manager', 0

120, 'Peter', 'Director', 0

300, 'Paul',  'Auxiliar', 1

]

;

[Personal_Data]:

LOAD *,ID as ID_PD INLINE [

ID, birthday, license_car

1000,01/10/1970, 1

900, 12/01/1980, 0

200, 23/05/1960, 0

120, 04/05/1982, 1

300, 11/10/1990, 1

]

;

COMMON:

LOAD * INLINE [

ID,hobby

1000,xyx

900,abc

200,efg

120,pla

300,gdf

];

left join (COMMON)

LINK_TABLE:

Load ID,ID_JD,Consultant as Consultant_License RESIDENT [Job_Data];

Concatenate

Load ID,ID_PD,license_car as Consultant_License RESIDENT [Personal_Data];

Drop Field ID FROM [Job_Data];

Drop Field ID FROM [Personal_Data];

View solution in original post

12 Replies
kumarkp412
Creator II
Creator II

Hi Tony,

Please find the below attachment and let me know.

Thanks

Kumar KVP

Not applicable
Author

In fact, what I want is internally filter my data model according to the checkbox, value (checked/unchecked). The expression of the checkbox may be another (I don't know).

So:

  • If I check this checkbox basically I want Qlik to automatically filter my data model in order to take into account only the data with "ID" 300 and 900 because "Consultant" field is marked as 1 (which come from table "Job_Data"), and those which come from table "Personal_Data", that is, those which have "ID" 120, 300 and 1000 I also want to include them in my data model because their "license_car" field is marked as 1. However I do not want to include in my data model those which have "ID" 200 and 900 (from table "Personal_Data") because in this case their "license_car" field is 0.

  

In other words, in this case, I want Qlik to include in my data model the data for IDs 300,900 (table "Job_Data") and also include data corresponding to IDs 120,300 and 1000 that come from table "Personal_Data". However, data for IDs 200 and 900 that come from table "Personal_Data" I want Qlick to automatically exclude from my data model.

  • If I uncheck this checkbox:
    • I want data model to take into account all the data that come from both tables independently of the values for "Consultant" and "license_car" fields.

Is it possible?

vinieme12
Champion II
Champion II

Check the Attached, you need a link table to manage this

Listbox

=if(Consultant_License=1,'Only consultants and/or license car')

/////// SCRIPT/////////////////////////

[Job_Data]:

LOAD *,ID as ID_JD INLINE [

ID, Name, Category, Consultant

1000, 'David', 'Auxiliar', 0

900, 'John', 'Analyst', 1

200, 'Anne', 'Manager', 0

120, 'Peter', 'Director', 0

300, 'Paul',  'Auxiliar', 1

]

;

[Personal_Data]:

LOAD *,ID as ID_PD INLINE [

ID, birthday, license_car

1000,01/10/1970, 1

900, 12/01/1980, 0

200, 23/05/1960, 0

120, 04/05/1982, 1

300, 11/10/1990, 1

]

;

LINK_TABLE:

Load ID,ID_JD,Consultant as Consultant_License RESIDENT [Job_Data];

Concatenate

Load ID,ID_PD,license_car as Consultant_License RESIDENT [Personal_Data];

Drop Field ID FROM [Job_Data];

Drop Field ID FROM [Personal_Data];

/////// End of SCRIPT/////////////////////////




link_Table.PNG

vinieme12
Champion II
Champion II

Hi rrodriguez75

Have you checked the attached?

I'd like to add that never use table box to verify the data as a table box is only a collection of listboxes and not exact representation of your data.

Not applicable
Author

Your solution works well. But this sample is a piece of my big data model. In my entire data model both tables, Job_Data and Personal_Data are related as well through a common table, let's say "Common". So in this case your solution generates a circular reference.

   Personal_Data <-------> LINK_TABLE <----------> Job_Data

               |                                                                       |

               |                                                                       |

               --------------------> COMMON <----------------------

Imagine "Common" table has columns:

- ID

- Hobby

But I do not want to replicate table "Common" to avoid the circular reference. Any method to avoid circular reference without replicating data?

vinieme12
Champion II
Champion II

Then let the COMMON Table behave like the link table

[Job_Data]:

LOAD *,ID as ID_JD INLINE [

ID, Name, Category, Consultant

1000, 'David', 'Auxiliar', 0

900, 'John', 'Analyst', 1

200, 'Anne', 'Manager', 0

120, 'Peter', 'Director', 0

300, 'Paul',  'Auxiliar', 1

]

;

[Personal_Data]:

LOAD *,ID as ID_PD INLINE [

ID, birthday, license_car

1000,01/10/1970, 1

900, 12/01/1980, 0

200, 23/05/1960, 0

120, 04/05/1982, 1

300, 11/10/1990, 1

]

;

COMMON:

LOAD * INLINE [

ID,hobby

1000,xyx

900,abc

200,efg

120,pla

300,gdf

];

left join (COMMON)

LINK_TABLE:

Load ID,ID_JD,Consultant as Consultant_License RESIDENT [Job_Data];

Concatenate

Load ID,ID_PD,license_car as Consultant_License RESIDENT [Personal_Data];

Drop Field ID FROM [Job_Data];

Drop Field ID FROM [Personal_Data];

View solution in original post

Not applicable
Author

Great! One last thing, the resulting table is called 'COMMON' but how can I change the table name (for example to RESULT_TABLE)? Using below is not working:

[RESULT_TABLE]:

left join (COMMON)

LINK_TABLE:

Load ID,ID_JD,Consultant as Consultant_License RESIDENT [Job_Data];

Concatenate

Load ID,ID_PD,license_car as Consultant_License RESIDENT [Personal_Data];

vinieme12
Champion II
Champion II

Change the Name of the Table you are joining to

using above sample;

[Job_Data]:

LOAD *,ID as ID_JD INLINE [

ID, Name, Category, Consultant

1000, 'David', 'Auxiliar', 0

900, 'John', 'Analyst', 1

200, 'Anne', 'Manager', 0

120, 'Peter', 'Director', 0

300, 'Paul',  'Auxiliar', 1

]

;

[Personal_Data]:

LOAD *,ID as ID_PD INLINE [

ID, birthday, license_car

1000,01/10/1970, 1

900, 12/01/1980, 0

200, 23/05/1960, 0

120, 04/05/1982, 1

300, 11/10/1990, 1

]

;

RESULT_TABLE:

LOAD * INLINE [

ID,hobby

1000,xyx

900,abc

200,efg

120,pla

300,gdf

];

left join (RESULT_TABLE)

LINK_TABLE:

Load ID,ID_JD,Consultant as Consultant_License RESIDENT [Job_Data];

Concatenate

Load ID,ID_PD,license_car as Consultant_License RESIDENT [Personal_Data];

Drop Field ID FROM [Job_Data];

Drop Field ID FROM [Personal_Data];

Not applicable
Author

But without modifying the inline table name.