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: 
ezec1807
Creator
Creator

help set analysis

Hello!
I need help.
I have to count a field with certain criteria. The periods (dates) of two fields of different tables must match.

Script:

/*Table 1
company, period_1
a, 201507
b, 201508
c, 201510
e, 201702
* /
Table 1:
load
company, // pk
period_1
from x (qvd);


/ * table2
company, period_2, channel
a, 201507, channel1
b, 201602, channel2
c, 201702, channel3
d, 201605, channel4
* /
table2:
load
company, // pk
period_2,
channel
from and (qvd);

The value of period_1 and period_2 comes as 201505, 201506, 201704, etc.
I am doing the expression in a table / bar chart. I want to do it with set analysis to avoid the if function, but I do not succeed.

The expression I'm looking for would be something like this:

Count ({<period_1 = {period_2}>} distinct channel)

The result I'm looking for is:

Count channel = 2 (channel1 and channel3)

Thank you very much!

6 Replies
Digvijay_Singh

What dimension you are using in bar/table chart?

ezec1807
Creator
Creator
Author

Sorry, as a dimension I'm using the period_name field in table "3" (which I did not add in the previous question).

/ * Table 3
period_1, period_name
201507, Jul-2015
201508, Aug-2015
201510, Oct-2015
201702, Feb-2017
* /
Table 3:
load
period_1, // pk
period_name
from z (qvd);

Thank you very much!

Digvijay_Singh

Also you would need change in script to get the desired result, since Company is different for one of the matching date, it won't be counted for different companies.

normally the set expression syntax when matching fields is something like this -

count({<period_1={"=period_1=period_2"}>}channel)

Digvijay_Singh

I don't think even Table 3 would help much, somehow you need to associate Table 1 and 2 through date, not company. Because your matching dates are having different companies. Since company is associating your two tables data, it won't allow the match.

See the sample attached, may be it can help you to make required changes.

Digvijay_Singh

Sample attached

ezec1807
Creator
Creator
Author

Thank you very much!!!
In case it was not clear: what I'm looking for is that when I select a value from the field "period_name" (which is associated with the period_1 field of table 1), I'll look for that same value in table 2 and when I find it return the channel.

Example:
I select Feb-2017, it will search table 2 for the 201702 value (which comes from table 1), and it returns channel 3.

The idea is not to modify the data model and calculate it with the set analysis.

Thank you very much again!!!