Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Not applicable
Author

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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you give us some sample data to try.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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

marcus_sommer

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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!