Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Sum in Script

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

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.

1 Solution

Accepted Solutions
Highlighted
Champion III
Champion III

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;

View solution in original post

5 Replies
Highlighted
Specialist II
Specialist II

Load event_name,

         Sum([Net Sold Qty]) as NetSoldQtySum

resident yourtablename

group by event_name;

Highlighted
Champion III
Champion III

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;

Highlighted
Specialist
Specialist

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';

Highlighted
Champion III
Champion III

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;

View solution in original post

Highlighted
Specialist
Specialist

This worked great, thank you.