topic Re: Calculation within Script in QlikView

Calculation within Script

Ahmed_Turnaround — Fri, 28 Feb 2020 10:32:13 GMT

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?

Re: Calculation within Script

Brett_Bleess — Wed, 01 Apr 2020 19:28:19 GMT

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

Re: Calculation within Script

sunny_talwar — Wed, 01 Apr 2020 19:37:01 GMT

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

Re: Calculation within Script

Ahmed_Turnaround — Thu, 02 Apr 2020 07:37:12 GMT

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