topic Re: Set Expression between tables not join in Reporting Service & Alerting

Set Expression between tables not join

Qlik_Cagri — Thu, 07 Apr 2022 07:54:47 GMT

Hi; Can anyone advise if this is actually possible? I'm certainly struggling.

There were two different table that i don't want to join (For performans etc.). For that i have to use set espression.

In the tables below, want to get the total amount of customers in Table2 whose segment is commercial in Table 1.

The SQL version is; Select sum(Cost) from Tablo2 where CostumerID in (Select CostumerID from Tablo1 where Segment = ‘Commercial’

Thanks.

Table1;

CostumerID	Segment	CostumerName
1	Commercial	Murat
2	Commercial	Hasan
3	Commercial	Ece
4	Commercial	Zeynep
5	Commercial	Murat
6	Client	Yasemin
7	Client	Emirhan
8	Client	Onur
9	Client	Şeyma
10	Client	Cansu

Table2;

CostumerID	Product	Cost
1	House	5000
2	Vehicle	10000
3	Gold	15000
4	FC	20000
5	House	25000
6	Vehicle	30000
7	Gold	35000
8	FC	40000
9	House	45000
10	Vehicle	50000

Re: Set Expression between tables not join

marcus_sommer — Thu, 07 Apr 2022 08:27:59 GMT

Within the UI you could use an expression like:

sum({< Segment = {'Commercial'}>} Cost)

- Marcus

Re: Set Expression between tables not join

Qlik_Cagri — Thu, 07 Apr 2022 09:01:00 GMT

Thank you for your answer. But two table not joined. Therefore "sum({< Segment = {'Commercial'}>} Cost)" expressions returns the entire sum, not just the commercial ones.

Re: Set Expression between tables not join

marcus_sommer — Thu, 07 Apr 2022 09:17:18 GMT

Both tables aren't associated per CustomerID? In some way the tables should be sensible merged or associated with each other.

- Marcus

Re: Set Expression between tables not join

Qlik_Cagri — Thu, 07 Apr 2022 11:52:08 GMT

Actually it can be, but I don't prefer to associate tables because of load performance, different usage or etc.

The tables are completely different and I want to add criteria from another table without establishing any relation.

Re: Set Expression between tables not join

marcus_sommer — Thu, 07 Apr 2022 12:01:54 GMT

It's not really clear for me what do you want to do - the provided information is confusing and contradictory.

Adding the relevant information - which customer is in which segment - could be easily done with a global mapping or a mapping to a single segment, like:

m: mapping load CustomerID, 1 from Table1 where Segment = 'Commercial';

Table2: load *, applymap('m', CustomerID, 0) as Flag from Table2;

and later in the UI maybe:

sum(Cost * Flag)

- Marcus