Calculation within Script

[Ahmed_Turnaround]

Hi,

I did some research and couldn't find the answer, so I thought I'd make a new topic and hope that hasn't broken any rules. I am trying to put a calculated field within my Load Script but I'm having trouble doing this. My script, currently is as follows:

GLBALANCEMASTER:
LOAD [GL BAL KEY],
[BALANCE FILE SUMMARY LEVEL],
[GL COMPANY ID],
[PROFIT CENTRE ID],
[GL ACCOUNT MAJOR ID],
[GL ACCOUNT MINOR ID],
[GL CURRENCY],
[YEAR END],
[OPENING BALANCE FOR YEAR],
[GL PERIOD NN],
[GL MOVEMENT BASE],
[GL PERIOD],
[CLOSING BALANCE BASE],
[GL MOVEMENT CONSOL1],
[GL MOVEMENT CONSOL2],
[CLOSING BALANCE CONSOL1],
[CLOSING BALANCE CONSOL2],
[GL ACCOUNT MINOR],
[GL BAL KEY] & '%' & [GL PERIOD NN] as [GL_BAL_KEY_2]
FROM
[E:\Qlikdata\IP1 Insight Pack 1.4\Data\TA.GLBalance.qvd]
(QVD);

MAPCG:
LOAD [GL ACCOUNT MAJOR ID],
[PROFIT CENTRE ID],
[Sub Category ID] as [SUB CATEGORY ID],
[Sub Category Sale or Cost ID] as [SUB CATEGORY SALE OR COST ID]
FROM
[E:\Qlikdata\IP1 Insight Pack 1.4\Data\TA.MAPCG.qvd]
(qvd);

SUBCATEGORY:
LOAD [Sub Category ID] as [SUB CATEGORY ID],
[Sub Category Description] as [SUB CATEGORY DESCRIPTION],
[Sub Category Sale or Cost ID] as [SUB CATEGORY SALE OR COST ID]
FROM
[E:\Qlikdata\IP1 Insight Pack 1.4\Data\TA.AccountSubCategory.qvd]
(qvd);

SALECOST:
LOAD [Sub Category Sale or Cost ID] as [SUB CATEGORY SALE OR COST ID],
[Sub Category Sale or Cost Description] as [SUB CATEGORY SALE OR COST DESCRIPTION]
FROM
[E:\Qlikdata\IP1 Insight Pack 1.4\Data\TA.AccountSaleCost.qvd]
(qvd);

JOIN (GLBALANCEMASTER)
LOAD * RESIDENT MAPCG;

JOIN (GLBALANCEMASTER)
LOAD * RESIDENT SUBCATEGORY;

JOIN (GLBALANCEMASTER)
LOAD * RESIDENT SALECOST;

DROP TABLE MAPCG;
DROP TABLE SUBCATEGORY;
DROP TABLE SALECOST;

I then want to calculate a figure based on whether a sub category ID matches a specific number. I have the following code, which I've put before the DROP TABLES command, but it doesn't seem to work:

CALCULATIONS:
LOAD
[SUB CATEGORY ID],
(SUM({<[SUB CATEGORY ID] = {'10'}>} [GL MOVEMENT BASE]) *-1) as [TEST TOTAL TRADE SALES]
RESIDENT (GLBALANCEMASTER)
GROUP BY [SUB CATEGORY ID];

Below is what is underlined in error:

Any ideas what I am doing wrong?

[Brett_Bleess]

Only thing of which I can think may be quote related, here is a Design Blog link to check on things there:

https://community.qlik.com/t5/Qlik-Design-Blog/Quotes-in-Set-Analysis/ba-p/1471824

If you need to do some further searching, you can use the following URL to have access to the search box:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

My post will kick things back up again too, so someone else may revisit and confirm or point out something else.

Regards,
Brett

[sunny_talwar]

Set analysis is a concept used on the front end of an application. Backend can use if statement like this

CALCULATIONS:
LOAD [SUB CATEGORY ID],
      -1* SUM(If([SUB CATEGORY ID] = 10, [GL MOVEMENT BASE])) as [TEST TOTAL TRADE SALES]
Resident GLBALANCEMASTER
Group By [SUB CATEGORY ID];

or you can do this

CALCULATIONS:
LOAD [SUB CATEGORY ID],
      -1 * SUM([GL MOVEMENT BASE]) as [TEST TOTAL TRADE SALES]
Resident GLBALANCEMASTER
Where [SUB CATEGORY ID] = 10
Group By [SUB CATEGORY ID];

[Ahmed_Turnaround]

Thanks guys, will review and get back to you all ASAP.