Applying two filters with a single request

[#1]

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!!

[kaushiknsolanki]

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

[#3]

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]

Can you give us some sample data to try.

Regards,

Kaushik Solanki

[#5]

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

[#7]

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

[#8]

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

[#9 · Accepted solution]

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!