Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
What dimension you are using in bar/table chart?
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!
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)
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.
Sample attached
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!!!