How to get row specific values into Set Analysis

[#1]

Hi All,

Been struggling with this problem for a few days and thought I'd share my pain...

I have two data sets. One containing a schedule, one containing some rules:

Schedule:

Channel, Date, Start Time, End Time, Duration, ScheduleCountry, Category

e.g.

Nickelodeon, 01/03/2014, 06:00:00, 06:59:59, 60:00, Canada, Animation

Nickelodeon, 01/03/2014, 07:00:00, 07:29:59, 30:00, USA, Live Action

(couple-hundred thousand rows in all)

Rules:

Channel, RuleCountry, Category, Percentage

Nickelodeon, Canada, All, 50

Nickelodeon, All, Animation, 40

(about 50 rules)

So, first rule says "the total time in the schedule for Nickelodeon must have 50% or higher with Canada as the Country value". I hard-coded this formula for just that rule. In my pivot table I have a formula that includes my current selected channel, year and month (year and month derived at load time) but only country code 2 (Canada):

=interval(timestamp#(sum({1<CHANNEL=P(CHANNEL),SCHEDULE_YEAR=P(SCHEDULE_YEAR), SCHEDULE_MONTH=P(SCHEDULE_MONTH),ScheduleCountry={2}>}SCHEDULE_DURATION_SECONDS),'ss'),'hh:mm:ss')

That gives me the correct total time! Woo! But...

Because I have about 50 different rules I don't want 50 hard-coded statements. I want to display in the rules table the total time dependent on the value of the columns in that particular rule row (and later I'll extrapolate this to a percentage). If I change the hard-coded value "2" into "RuleCountry" I just get 00:00:00. Attached a pic of how it looks now...

Any advice would be appreciated. I'm pretty new to QlikView so just getting this hard-coded set analysis statement working was mind-blowing!

[Anonymous]

Hello, Antony.

Unfortunately Set Analysis is evaluated only once per chart, it's not in a per line basis, so it won't work for you. What I advise is for you to create a composite key CHANNEL_COUNTRY to link the Schedule table and the Rules table. That way you won't need the last part regarding ScheduleCountry in the Set Analysis.

Hope it helps.

[#3]

Hmm...I have several sets of rules - some for country, some for category, some for content, etc. 8 or so different fields in total. I guess I could create a rule_map for each field as a character string. I have the rules and schedule linked through channel right now so I guess my composite key would be:

channel, country, exhibition, source, etc, etc. (00 being an "All" wildcard) - e.g. 'NICK0200000000000000'

I'll give it a go and let you know...