Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

12021656627z
New Contributor II

Filtering using values from another table, in expression

Hi all,

I currently have a main table for all my invoices, but i have 2 groups of customers, I'll name them Group A & Group B for simplicity.

I would like to do up a bar graph showing total invoice sales for these 2 group of customers.

They're all linked together by their customerID, below is a rough example of what the table structure is like(the actual tables contains thousands of customerIDs in Main invoice table, GroupA & GroupB):

Main invoice table:

customerIDInvoiceNoCustomer NameAmount$
10011234A10
10021235B20
10031236C20
10041237D30
............

Group A table:

A.customerIDA.Customer Name
1001A
1003C
......

Group B table:

B.customerIDB.Customer Name
1002B
1004D
......

From my knowledge, I know that you can filter by sum({$<customerID={"1001","1003"}>} [Amount$])

But this is only practical and applicable to small tables, what I am currently working on is a table with lots of customerIDs.

Is there any way to filter by using another table's values?

Any help or comments will be greatly appreciated!

1 Solution

Accepted Solutions
maxsheva
Contributor II

Re: Filtering using values from another table, in expression

Probably you are looking for Alternate States in Qlik Sense

You may check this solution without using an external extension

Using Alternate State in QLikSense (without Extensions)


So you may try sum({$<customerID=P([A.customerID])>} [Amount$])

10 Replies

Re: Filtering using values from another table, in expression

Assign groups to the customer in the script itself (using mapping load or if statement) and then you can just do this

Sum({$<Group={'Group A'}>} [Amount$])

maxsheva
Contributor II

Re: Filtering using values from another table, in expression

Hi, please try P and E functions. You may get info from Stefan's post https://community.qlik.com/thread/68931

For example

sum({$<customerID=P({1<customerID={'FieldFromOtherTable'}>} customerID)>} [Amount$])

It can provide you dynamic selection.

zebhashmi
Valued Contributor

Re: Filtering using values from another table, in expression

your data model is not in shape, how you can have it Qlik. change the Customer Name as GB Customer Name and sum on that.

Regards

12021656627z
New Contributor II

Re: Filtering using values from another table, in expression

Hi Max,

Thanks for your valuable reply, I've tried the formula given but it resulted in an error, but I have a feeling that the formula you gave is logically correct, and its just my application that is wrong.

Could I ask of you to kindly explain to me how it works?

I've tried sum({$<customerID=P({1<customerID={'A.customerID'}>} customerID)>} [Amount$])

where A.customerID is the customerID field of GroupA, which I've associated in the data model manager as such.

12021656627z
New Contributor II

Re: Filtering using values from another table, in expression

Hi Hashmi,

What I've given are the base table fields before association, sorry for not being clear with my question. I've edited my question for better clarification.

Best Regards,

Yi Xuan

12021656627z
New Contributor II

Re: Filtering using values from another table, in expression

Hi Sunny,

Is this applicable for tables with regularly changing data? I might just use this if nothing else works

Thanks alot!

Best Regards,

Yi Xuan

arvind1494
Valued Contributor

Re: Filtering using values from another table, in expression

Sum({$<Group={'Group A'}>} [Amount$])



try above in your exprerssion.

agigliotti
Honored Contributor II

Re: Filtering using values from another table, in expression

I suggest you to create a new field in the Customer table with the customer's group he belongs to (A/B).

Finally in your bar chart you can use it as dimension value.

maxsheva
Contributor II

Re: Filtering using values from another table, in expression

Probably you are looking for Alternate States in Qlik Sense

You may check this solution without using an external extension

Using Alternate State in QLikSense (without Extensions)


So you may try sum({$<customerID=P([A.customerID])>} [Amount$])

Community Browser