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: 
Anonymous
Not applicable

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
Creator II
Creator II

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$])

View solution in original post

10 Replies
sunny_talwar

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
Creator II
Creator II

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

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

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



try above in your exprerssion.

agigliotti
Partner - Champion
Partner - Champion

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
Creator II
Creator II

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$])