topic Re: Sum in Script in QlikView

Sum in Script

evansabres — Wed, 24 Oct 2018 15:08:17 GMT

I have a large data set, of which some of the important fields are event_name and [Net Sold Qty]

For a particular event_name, any time a ticket is sold, a row of data is created:

event_name Net Sold Qty

ABC123 2

ABC123 3

DEF456 7

DEF456 5

GHI987 4

and so on.

What I would like to be able to do in my script is to aggregate all of the [Net Sold Qty] for an event_name prior to be loading into my application. So that when I view the data, it would appear as

event_name Net Sold Qty

ABC123 5

DEF456 10

GHI987 8

How would this be done in the script? I can certainly do this within the application but would like to do this in the script for the purpose of this particular application.

Re: Sum in Script

neelamsaroha157 — Wed, 24 Oct 2018 15:11:55 GMT

Load event_name,

Sum([Net Sold Qty]) as NetSoldQtySum

resident yourtablename

group by event_name;

Re: Sum in Script

vishsaggi — Wed, 24 Oct 2018 15:12:13 GMT

May be try this:

SourceTable:

LOAD event_name, NetSoldQty

From Source;

LEFT JOIN(SourceTable)

LOAD event_name,

Sum(NetSoldQty) AS SaleQty

Resident SourceTable

Group By event_name;

Re: Sum in Script

evansabres — Wed, 24 Oct 2018 15:31:10 GMT

This is currently the Structure of my script. Where would I add in your recommendation or otherwise alter my script?

Ticketing:

LOAD

[Net Sold Qty] AS bufQTYTTL,

purchasePRICE AS bufPurchPrice,

block_purchase_price AS bufREV,

transDATE AS bufTRANSDTE,

PriceCode AS bufPriceCDE,

team AS bufOpp,

LicenseRev AS bufLICREV,

event_date AS bufEventDTE,

event_time AS bufEventTME,

event_name AS bufEVENTNME,

comp_name AS bufCMPNME,

compTYPE AS bufCMPTYPE

FROM [DATA SOURCE];

LEFT JOIN (Ticketing)

Schedule:

LOAD

event_name AS bufEVENTNME,

WEEKDAY,

MONTH,

DAY,

YEAR,

TIME,

[Game Date] AS bufGMEDTE,

Opponent AS bufGMEDESC,

LOCATION,

CITY,

ST,

TYPE,

[VAR. PRICE]

FROM [DATA SOURCE];

TicketingNew:

LOAD

bufGMEDTE,

bufEVENTNME,

bufGMEDESC,

bufQTYTTL,

bufREV,

'buf' AS brandNME

RESIDENT 'Ticketing';

DROP TABLE 'Ticketing';

Re: Sum in Script

vishsaggi — Wed, 24 Oct 2018 15:45:28 GMT

Try this?

Ticketing:

LOAD

[Net Sold Qty] AS bufQTYTTL,

purchasePRICE AS bufPurchPrice,

block_purchase_price AS bufREV,

transDATE AS bufTRANSDTE,

PriceCode AS bufPriceCDE,

team AS bufOpp,

LicenseRev AS bufLICREV,

event_date AS bufEventDTE,

event_time AS bufEventTME,

event_name AS bufEVENTNME,

comp_name AS bufCMPNME,

compTYPE AS bufCMPTYPE

FROM [DATA SOURCE];

LEFT JOIN (Ticketing)

Schedule:

LOAD

event_name AS bufEVENTNME,

WEEKDAY,

MONTH,

DAY,

YEAR,

TIME,

[Game Date] AS bufGMEDTE,

Opponent AS bufGMEDESC,

LOCATION,

CITY,

ST,

TYPE,

[VAR. PRICE]

FROM [DATA SOURCE];

NoConcatenate

TicketingNew:

LOAD

bufGMEDTE,

bufEVENTNME,

bufGMEDESC,

bufQTYTTL,

bufREV,

'buf' AS brandNME

RESIDENT 'Ticketing';

DROP TABLE 'Ticketing';

LEFT JOIN(TicketingNew)

LOAD bufEVENTNME,

Sum(bufQTYTTL) AS SumQTYTTL

Resident TicketingNew

Group By bufEVENTNME;

Re: Sum in Script

evansabres — Wed, 24 Oct 2018 16:06:31 GMT

This worked great, thank you.