Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to get row specific values into Set Analysis

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!

2 Replies
Highlighted
Creator III
Creator III

Re: How to get row specific values into Set Analysis

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.

Highlighted
Not applicable

Re: How to get row specific values into Set Analysis

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