topic Re: Expression Not Displaying Value in App Development

Expression Not Displaying Value

evansabres — Wed, 28 Aug 2019 18:31:55 GMT

I have created a chart with several dimensions and expressions. I am attempting to add in a relatively simple expression of =CURRENTPRICE however, when I apply the expression, not data is displayed.

When I add in an aggregation method such as MIN, MAX, SUM, etc. I get data.

Any ideas on where I am going wrong would be helpful. I have attached my data model and below are the dimensions I am using as well as a small sample of what the table looks like

Game Date	Opponent	Tier	Price Scale	Price
9/15/19	ABC	1	Lower	-
9/15/19	ABC	1	Upper	-
9/15/19	ABC	1	Club	-
9/15/19	ABC	1	Suite	-

Re: Expression Not Displaying Value

dplr-rn — Wed, 28 Aug 2019 18:42:35 GMT

I am assuming you have more than 1 value for CurrentPrice per row. If so that will not display the value and you need an aggregration function

i.e. combination of Game,Opponent,tie,pricescale may have more than 1 currentprice

Game Date	Opponent	Tier	Price Scale	Price
9/15/19	ABC	1	Lower

Re: Expression Not Displaying Value

evansabres — Wed, 28 Aug 2019 18:51:32 GMT

There should only be one CURRENTPRICE per row, based on the Game Date and Price Scale

Do I need to introduce a set expression? If so, what would they structure be?

Re: Expression Not Displaying Value

dplr-rn — Wed, 28 Aug 2019 18:52:38 GMT

depends on your data. Share data sample similar to your actual data and i can try and help

Re: Expression Not Displaying Value

evansabres — Wed, 28 Aug 2019 19:02:15 GMT

Hope this helps

Re: Expression Not Displaying Value

dplr-rn — Wed, 28 Aug 2019 19:18:51 GMT

Looking at it i would check your data model. You have multiple columns with values for Price Scale

PPPRICESCALEDESC

PRICESCALEDESC

have a feeling something is messed up while you are loading and you haven't modeled it correctly.

Load this data into a sample app exaclty like your load script and share the qvf file if you can

Re: Expression Not Displaying Value

evansabres — Wed, 28 Aug 2019 19:20:39 GMT

I have them labeled differently so as not to create a synthetic key and/or loop in the script.

Re: Expression Not Displaying Value

dplr-rn — Wed, 28 Aug 2019 19:27:27 GMT

Are you using the right column in your table?

Add the CURRENTPRICE column as a dimension and check.

Re: Expression Not Displaying Value

evansabres — Wed, 28 Aug 2019 19:53:33 GMT

When I add that field (CURRENT PRICE) in as a Dimension, the app generates an error of 'Out of Calculation Memory'

Re: Expression Not Displaying Value

dplr-rn — Wed, 28 Aug 2019 19:55:52 GMT

that hints at a modeling issue .
As requested before Share a sample app with your exact load script and sample data. and i can check

Re: Expression Not Displaying Value

evansabres — Wed, 28 Aug 2019 19:58:01 GMT

I do not have Qlik Sense desktop unfortunately

Re: Expression Not Displaying Value

dplr-rn — Wed, 28 Aug 2019 20:08:22 GMT

It's free 😉

Can you share script?

Re: Expression Not Displaying Value

evansabres — Wed, 28 Aug 2019 20:11:14 GMT

Pacing:
select
TO_CHAR(po.created_date, 'MM/DD/YYYY') AS ScottsDate,
es.last_updated_date,
e.event_code AS EVENTCODE,
e.event_date,
e.price_structure_id AS PRICESTRUCTUREID,
a.agency_code,
bt.buyer_type_code,
bt.description AS buyer_type_desc,
c.channel_code,
c.description AS channel_desc,
ps.price_scale_code AS PRICESCALECDE,
ps.description AS PRICESCALEDESC,
es.price,
sec.section_code,
s.row_,
bt.description AS BuyerTypeDesc,
s.seat_number,
COUNT(1) AS QTY

from
agency a,
buyer_type bt,
channel c,
event e,
event_category ec,
event_seat es,
order_line_item oli,
patron_order po,
price_scale ps,
seat s,
section sec

where
oli.order_id = po.order_id
and oli.usage_event_id = e.event_id
and es.event_id = e.event_id
and es.order_id = po.order_id
and es.order_line_item_id = oli.order_line_item_id
and po.created_by_agency_id = a.agency_id
and a.channel_id = c.channel_id
and e.event_category_id = ec.event_category_id
and sec.section_id = es.section_id
and s.seat_id = es.seat_id
and es.buyer_type_id = bt.buyer_type_id
and ps.price_scale_id = es.price_scale_id

and oli.market_type_code = 'P'
and oli.transaction_type_code in ( 'SA', 'ES', 'CS' )
and c.channel_code in ( 'BOXOFF', 'INTERNET', 'PHONE' )
and ec.event_category_code = 'SPORT'
and e.event_id IN ('5636', '5637', '5638', '5639', '5640', '5641', '5642', '5644', '5645', '5646', '5647', '5648', '5649', '5650', '5651', '5652', '5653', '5654', '5655', '5656', '5657',
'5658', '5659', '5660', '5661', '5662', '5663', '5664', '5665', '5666', '5667', '5668', '5669', '5670', '5671', '5672', '5673', '5674', '5675', '5769')

GROUP BY
po.created_date,
es.last_updated_date,
e.event_code,
e.event_date,
e.price_structure_id,
a.agency_code,
bt.buyer_type_code,
bt.description,
c.channel_code,
c.description,
ps.price_scale_code,
ps.description,
es.price,
sec.section_code,
s.row_,
bt.description,
s.seat_number;

CurrentPrices:
select
E.EVENT_CODE AS EVENTCODE,
PS.PRICE_SCALE_CODE AS PPPRICESCALECDE,
PS.DESCRIPTION AS PPPRICESCALEDESC,
PP.PRICE AS CURRENTPRICE

FROM
PRICE_POINT PP,
PRICE_SCALE PS,
EVENT E

WHERE
PP.PRICE_SCALE_ID = PS.PRICE_SCALE_ID
AND PP.PRICE_STRUCTURE_ID = E.PRICE_STRUCTURE_ID
AND PP.BUYER_TYPE_ID = '1'
AND PP.PRICE_STRUCTURE_ID IN ('11786', '11787', '11788', '11789', '11790', '11791', '11792', '11794', '11795', '11796', '11797', '11798', '11799', '11800', '11801', '11802', '11803', '11804', '11805', '11806', '11807', '11808', '11809', '11810', '11811', '11812', '11813', '11814', '11815', '11816', '11817', '11818', '11819', '11820', '11821', '11822', '11823', '11824', '11825', '11844')
AND PS.PRICE_SCALE_CODE IN ('CLUB', 'CLUBEN', '100PRF', '100LV2', '100LV3', '100LV4', '300LV1', '300LV2', '300LV3', '300LV4', '300LV5', '300LV6')

GROUP BY
E.EVENT_CODE,
PS.PRICE_SCALE_CODE,
PS.DESCRIPTION,
PP.PRICE

ORDER BY EVENT_CODE;

SCHEDULE:

LOAD
"GAME ID",
"Home Game",
"WEEKDAY",
"MONTH",
"DAY",
"Game Date",
"YEAR",
"TIME",
"Event ID",
"Event Code" AS EVENTCODE,
SUBJECT,
Opponent,
"Game Description",
LOCATION,
CITY,
ST,
"TYPE",
"VAR. PRICE" AS TIER,
"VAR. Price Numeric"
FROM [data source]

Manifest:

LOAD
"Price Scale Code" AS PRICESCALECODE,
"Price Scale Description" AS PRICESCALEDESC,
"Price Scale ID" AS priceSCALEID,
SECTION_GROUP_DESCRIPTION AS sectionGROUP,
"Section" AS maniSECTION,
"Row" AS maniROW,
"Seat Number" AS maniSEAT,
"Section Capacity" AS secCAP,
"Price Level Capacity" AS plttlCAP,
"After Kills PL Capacity" AS PLCAP,
"Section Delimit" AS secDELIMIT,
"Section Overall" AS secOVERALL
FROM [data source];