• Replies 5 replies
  • Subscribers 336 subscribers
  • Views 96 views
  • Users 0 members are here
Related

Puzzle: complicated sums in T_KEYWORD List Criteria

Gawain Lavers
$organization

Here's the scenario: I'm building a new criteria set for ticketing information, and one of the things I want to add is Production Element Keywords.  Now, this creates lots of duplicate rows, as multiple keywords can be attached to a single performance (not to mention packages!  Almost forgot to do those!)  Still as a List Critieria that's mostly no problem as I'm trying to pull customer_nos at the end of the day.  But!  There are a few fields where I want to be able to search on an aggregate value.  One is # of Unique Perfs: that one is easy, COUNT(distinct perf_no) is going to work no matter how many times the SLI row is inflated.  But I'd also love to have a "Paid Amount" criteria as well.  I'd need something like the SUM of the MAX of each (sli_no, keyword_id) grouping?  Does anyone know a clever way to get this, or will SUM and sli_no duplication always be incompatible?

Parents
  • $organization

    I should perhaps define my problem a little better.  I'm creating a new set of criteria for list manager, where I want to allow users to identify customers based on, among other things, the keywords attached to performances they have tickets to.  So I might create a view like this (again very simple, obviously keywords can be attached to different levels of the production element tree, etc.):

    CREATE VIEW SLI_TKW_TEST
AS
select
	o.customer_no,
	o.order_no,
	sli.sli_no,
	sli.perf_no,
	sli.paid_amt,
	kw.tkw
from T_SUB_LINEITEM as sli
	inner join T_ORDER as o on o.order_no = sli.order_no
	inner join TX_INV_TKW as kw on kw.inv_no = sli.perf_no
where
	sli.sli_status = 12
;

    Now, if I want to create a list criteria to find people who have a ticket to show with a certain keyword attached, I can create a criteria in T_KEYWORD that looks like this:

    description = "Performance Keyword"
    detail_col = '!.tkw'
    ref_tbl = 'TR_TKW'
    ref_IDCol = 'id'
    ref_DescCol = 'description'

    And if I want to be able to say something like "I want everyone who has a tickets to two or more performances", I could use a criteria like this:

    description = "Total Unique Performances"
    detail_col = 'COUNT(distinct !.perf_no)'
    ref_tbl = null
    ref_IDCol = null
    ref_DescCol = null

    But if I want to say, "I want everyone who has spent over $50 on a ticket" I can do this:

    description = "Total Paid Amount"
    detail_col = '!.paid_amt'
    ref_tbl = null
    ref_IDCol = null
    ref_DescCol = null

    But if I say "I want everyone who ahs spent over $100 on tickets to a performance" this won't work:

    description = "Total Paid Amount"
    detail_col = 'SUM(!.paid_amt)'
    ref_tbl = null
    ref_IDCol = null
    ref_DescCol = null

    because that sum is going to be multiplied by the number of keywords associated with the performance.  The number of unique performances works because I am counting and can count unique ids, which will be the same no matter how many times the sli rows are duplicated, but I can't find a way to only sum once for each sli_no, at least in the context of configuring a list criteria row in T_KEYWORD.  Now obviously I can create a new view with a grouping, but then it won't be part of the same criteria set and you won't be able to use the criteria together.

Reply
  • $organization

    I should perhaps define my problem a little better.  I'm creating a new set of criteria for list manager, where I want to allow users to identify customers based on, among other things, the keywords attached to performances they have tickets to.  So I might create a view like this (again very simple, obviously keywords can be attached to different levels of the production element tree, etc.):

    CREATE VIEW SLI_TKW_TEST
AS
select
	o.customer_no,
	o.order_no,
	sli.sli_no,
	sli.perf_no,
	sli.paid_amt,
	kw.tkw
from T_SUB_LINEITEM as sli
	inner join T_ORDER as o on o.order_no = sli.order_no
	inner join TX_INV_TKW as kw on kw.inv_no = sli.perf_no
where
	sli.sli_status = 12
;

    Now, if I want to create a list criteria to find people who have a ticket to show with a certain keyword attached, I can create a criteria in T_KEYWORD that looks like this:

    description = "Performance Keyword"
    detail_col = '!.tkw'
    ref_tbl = 'TR_TKW'
    ref_IDCol = 'id'
    ref_DescCol = 'description'

    And if I want to be able to say something like "I want everyone who has a tickets to two or more performances", I could use a criteria like this:

    description = "Total Unique Performances"
    detail_col = 'COUNT(distinct !.perf_no)'
    ref_tbl = null
    ref_IDCol = null
    ref_DescCol = null

    But if I want to say, "I want everyone who has spent over $50 on a ticket" I can do this:

    description = "Total Paid Amount"
    detail_col = '!.paid_amt'
    ref_tbl = null
    ref_IDCol = null
    ref_DescCol = null

    But if I say "I want everyone who ahs spent over $100 on tickets to a performance" this won't work:

    description = "Total Paid Amount"
    detail_col = 'SUM(!.paid_amt)'
    ref_tbl = null
    ref_IDCol = null
    ref_DescCol = null

    because that sum is going to be multiplied by the number of keywords associated with the performance.  The number of unique performances works because I am counting and can count unique ids, which will be the same no matter how many times the sli rows are duplicated, but I can't find a way to only sum once for each sli_no, at least in the context of configuring a list criteria row in T_KEYWORD.  Now obviously I can create a new view with a grouping, but then it won't be part of the same criteria set and you won't be able to use the criteria together.

Children
No Data