Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Not applicable

Applying two filters with a single request

Hi everybody,

I've got a Pivot Table and I use two different filters on it. Assume that the table contains the columns Age, Work, Hobbies, Name, Surname and I currently use two filters on it: first I select the name of a person and then I select his surname (imagine I've got 100 values in the table, selecting the name I get 20 values and then selecting the surname I get 1 value). I use two different filters to do it, but I'd like to create one single filter that I can use to select Name and Surname once and so I would have for each Name value the possible Surname values (like a left join). I tried to add a section in the load script, but I can't figure out how to get what I'd like to have, something like [NAME]&' '&[SURNAME]. The problem is that name and surname are in two different tables and I have in table A [nameID, surnameID], in table B [nameID, name] and in table C [surnameID, surname], so I think I should join the values.ù

Thanks again!!

1 Solution

Accepted Solutions
Highlighted
Not applicable

Re: Applying two filters with a single request

Hi everybody,

I solved the problem adding this in the data load editor:

LIB CONNECT TO 'DATABASE';

[TABLE_A]:

LOAD [NAME],

[NAME_ID];

SELECT "NAME",

"NAME_ID"

FROM "USER_DB"."TABLE_A";

[TABLE_B]:

LOAD [SURNAME],

[SURNAME_ID];

SELECT "SURNAME",

"SURNAME_ID"

FROM "USERDB"."TABLE_B";

[USERS]:

LOAD [IDENTIFIER],

[NAME_ID],

[SURNAME_ID];

SELECT "IDENTIFIER",

"NAME_ID",

"SURNAME_ID"

FROM "USERDB"."USERS";

[TABLE_C]:

NoConcatenate

Load [IDENTIFIER], [NAME_ID], [SURNAME_ID]

Resident [USERS];

Left Join

Load [NAME], [NAME_ID]

Resident [TABLE_A];

Left Join

Load [SURNAME], [SURNAME_ID]

Resident [TABLE_B];

Drop Tables [TABLE_A], [TABLE_B], [USERS];

[TABLE_D]:

Load [NAME]&'-'&[SURNAME],

[IDENTIFIER]

Resident [TABLE_C];

Drop Table [TABLE_C];

And then I added the [NAME]&'-'&[SURNAME] filter in the app.

Hope this helps!

View solution in original post

8 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Applying two filters with a single request

Hi,

If your selection filter is going to give you one value i.e 1 value for Name and 1 Value for Surname then you can try below expression using current data model with new field [NAME]&' '&[SURNAME] as NameSurname

Count({<NameSurname = {"$(=if(Getpossiblecount(NameSurname )=1,only(NameSurname ),'*') )"}>}NameSurname )

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

Re: Applying two filters with a single request

Hi,

thank you for your fast reply, but I don't know how many values can I have, each name can have a different number of surnames

Highlighted
MVP & Luminary
MVP & Luminary

Re: Applying two filters with a single request

Can you give us some sample data to try.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

Re: Applying two filters with a single request

I'm sorry, but unfortunately I can't share my data I've actually got 17 milions data and I can't share that.

Thank you and regards

Highlighted
MVP & Luminary
MVP & Luminary

Re: Applying two filters with a single request

It wasn't meant to upload a whole application else: Preparing examples for Upload - Reduction and Data Scrambling.

Beside them I suggest to de-normalize your data-structure and to create one single dimension-table of your personal-data (quite probably there are many further tables with the countries, addresses and so on) then there are no advantages of using fully normalized structures within qlik - rather the opposite.

- Marcus

Highlighted
Not applicable

Re: Applying two filters with a single request

I'm sorry and thank you for the link, but I'm analyzing sensible data that can't be shared, the number is not the problem

Highlighted
Not applicable

Re: Applying two filters with a single request

Should I add a new calculated column within the main table with the fields that I'd like to aggregate?

Highlighted
Not applicable

Re: Applying two filters with a single request

Hi everybody,

I solved the problem adding this in the data load editor:

LIB CONNECT TO 'DATABASE';

[TABLE_A]:

LOAD [NAME],

[NAME_ID];

SELECT "NAME",

"NAME_ID"

FROM "USER_DB"."TABLE_A";

[TABLE_B]:

LOAD [SURNAME],

[SURNAME_ID];

SELECT "SURNAME",

"SURNAME_ID"

FROM "USERDB"."TABLE_B";

[USERS]:

LOAD [IDENTIFIER],

[NAME_ID],

[SURNAME_ID];

SELECT "IDENTIFIER",

"NAME_ID",

"SURNAME_ID"

FROM "USERDB"."USERS";

[TABLE_C]:

NoConcatenate

Load [IDENTIFIER], [NAME_ID], [SURNAME_ID]

Resident [USERS];

Left Join

Load [NAME], [NAME_ID]

Resident [TABLE_A];

Left Join

Load [SURNAME], [SURNAME_ID]

Resident [TABLE_B];

Drop Tables [TABLE_A], [TABLE_B], [USERS];

[TABLE_D]:

Load [NAME]&'-'&[SURNAME],

[IDENTIFIER]

Resident [TABLE_C];

Drop Table [TABLE_C];

And then I added the [NAME]&'-'&[SURNAME] filter in the app.

Hope this helps!

View solution in original post