topic Re: Date Call-Out in App Development

Date Call-Out

EvanBarrick — Wed, 12 Dec 2018 20:05:11 GMT

I have a chart that uses the dimension of 'Transaction Date' that shows ticket sales (Formatted as MM/DD/YYYY

I want to call-out dates that were events. For example, there was an event on 10/15/2018.

The events all come from another file, that field is called 'Event Date' and is also formatted as 'MM/DD/YYYY

Looking for ideas on how to do this? Would I need to create a mapping table?

Re: Date Call-Out

chriscammers — Wed, 12 Dec 2018 21:22:25 GMT

I think there a couple of ways to accomplish what you are talking about and they depend on what additional requirements you have around events. For example if all you want to do is flag dates as "Having an Event" then a simple mapping table would probably do the trick. If you want to be able to list the events that happened then I would probably link a separate event table to your transaction table, then you could use functions like Concat or first sorted value to show events on a specific day.

Re: Date Call-Out

EvanBarrick — Wed, 12 Dec 2018 21:53:33 GMT

I would like to flag a 'Transaction Date' as having an event.

Re: Date Call-Out

EvanBarrick — Thu, 13 Dec 2018 14:52:48 GMT

This is my script (the select part anyways) Where and how would I apply a map

select
TO_CHAR(po.created_date, 'MM-DD-YYYY'), ("Transaction Date")
es.last_updated_date,
e.event_code AS EVENTCODE,
e.event_date,
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,
ps.description AS price_scale_desc,
es.price,
sec.section_code,
s.row_,
s.seat_number,
COUNT(1) AS QTY

Re: Date Call-Out

chriscammers — Thu, 13 Dec 2018 15:55:18 GMT

You'll want to use a mapping table to find the events the way I have it coded the applymap function will return 1 if the transaction date is in the event map and a zero if it is not.

//Create a mapping table
EventMap:
Mapping
Load * Inline [
EventDate,EventCount
01/01/2017,1
02/14/2018,1
03/15/2017,1
11/06/2016,1
];

//The use a preceding load to lookup the events 
Load
ApplyMap('EventMap',TransactionDate,0) as EventCounter,
...
seat_number;
SQL Select...;

Re: Date Call-Out

EvanBarrick — Thu, 13 Dec 2018 16:33:11 GMT

I built the map and them am applying to my script, running into a syntax issue, any idea where I am going wrong?

select
TO_CHAR(po.created_date, 'MM-DD-YYYY'),
ApplyMap('EventMap', (TO_CHAR(po.created_date, 'MM-DD-YYYY'),0))) AS EventCounter,
es.last_updated_date,
e.event_code AS EVENTCODE,
e.event_date,
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,
ps.description AS price_scale_desc,
es.price,
sec.section_code,
s.row_,
s.seat_number,
COUNT(1) AS QTY

Re: Date Call-Out

chriscammers — Thu, 13 Dec 2018 16:47:17 GMT

you are adding a qlik function into the SQL part of the script. you need to use a preceeding load.

I think the documentation in the help files should give you a good overview.

The basics are you can use the SQL as a source for a qlik "Load" statement and it goes something like this

MyTable:
Load
Field1,
Field2,
Applymap('Mapname',TransactionDate) as NewField
Field4; //note the semicolin comes before the "from" this is the end of the preceeding load.
SQL Select * from SomeTable; //this SQL statement serves as the from for the above load

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/preceding-load.htm

Re: Date Call-Out

EvanBarrick — Thu, 13 Dec 2018 17:05:50 GMT

I am sure this is correct, just cannot get it to function on my end

Re: Date Call-Out

chriscammers — Thu, 13 Dec 2018 17:08:02 GMT

could you post your full sql query? I'll post a sample for you.

Re: Date Call-Out

EvanBarrick — Thu, 13 Dec 2018 18:19:13 GMT

select
TO_CHAR(po.created_date, 'MM-DD-YYYY'),
es.last_updated_date,
e.event_code AS EVENTCODE,
e.event_date,
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,
ps.description AS price_scale_desc,
es.price,
sec.section_code,
s.row_,
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 trunc(po.created_date) >= ('01-JAN-2018')
and trunc(po.created_date) <= ('08-APR-2019')
and oli.market_type_code = 'P'
and oli.transaction_type_code in ( 'SA', 'ES', 'CS' ) -- sale, claimsale or exchangesale
and c.channel_code in ( 'BOXOFF', 'INTERNET', 'PHONE' )
and ec.event_category_code = 'TYPE'
and e.event_id >= '5039' and e.event_id <= '5079'

GROUP BY
po.created_date,
es.last_updated_date,
e.event_code,
e.event_date,
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_,
s.seat_number;

Re: Date Call-Out

chriscammers — Thu, 13 Dec 2018 22:39:23 GMT

//Create a mapping table
EventMap:
Mapping
Load * Inline [
EventDate,EventCount
01/01/2017,1
02/14/2018,1
03/15/2017,1
11/06/2016,1
];

//The use a preceding load to lookup the events 
Load *,
ApplyMap('EventMap',TransactionDate,0) as EventCounter;
SQL
select
TO_CHAR(po.created_date, 'MM-DD-YYYY'),
es.last_updated_date,
e.event_code AS EVENTCODE,
e.event_date,
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,
ps.description AS price_scale_desc,
es.price,
sec.section_code,
s.row_,
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 trunc(po.created_date) >= ('01-JAN-2018')
and trunc(po.created_date) <= ('08-APR-2019')
and oli.market_type_code = 'P'
and oli.transaction_type_code in ( 'SA', 'ES', 'CS' ) -- sale, claimsale or exchangesale
and c.channel_code in ( 'BOXOFF', 'INTERNET', 'PHONE' )
and ec.event_category_code = 'TYPE'
and e.event_id >= '5039' and e.event_id <= '5079'

GROUP BY
po.created_date,
es.last_updated_date,
e.event_code,
e.event_date,
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_,
s.seat_number;

Re: Date Call-Out

EvanBarrick — Fri, 14 Dec 2018 16:34:18 GMT

Thank you your assistance with this. When I edit the script as you recommended, I get an error of 'map_id not found'