Calculation within Script failing

[Ahmed_Turnaround]

Hi All,

Hope you're all well on this lovely Friday morning in the UK! I was wondering if anyone can assist with helping me figure out why my calculation in my script is in error?

I have the following code:

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;

After this, I have

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

However, the sum is being shown in error:

What am I doing wrong?

Any assistance is greatly appreciated.

Many thanks,

A

[m_woolf]

I don't think you can use set analysis in script.

SUM({<[SUB CATEGORY ID] = {'10'}>} [GL MOVEMENT BASE]) *-1) as [TEST TOTAL TRADE SALES]

Maybe:

sum(if([SUB CATEGORY ID] = '10',[GL MOVEMENT BASE]) *-1) as [TEST TOTAL TRADE SALES]

[Ahmed_Turnaround]

Hi,

The calculation is no longer in error now but it gets an error when it runs. I'll try and figure out why, but can you see anything obvious or what this error message relates to?

Thanks for your help and thanks in advance.

[Ahmed_Turnaround]

I took the brackets out of GLBALANCEMASTER and the error went but now I have this error:

Invalid expression
CALCULATIONS:
LOAD
[SUB CATEGORY ID],
sum(if([SUB CATEGORY ID] = '10',[GL MOVEMENT BASE]) *-1) as [TEST TOTAL TRADE SALES]
RESIDENT GLBALANCEMASTER

[jonathandienst]

I think you may have removed this too:

GROUP BY [SUB CATEGORY ID];

Add it back.

[Ahmed_Turnaround]

Seems to have worked, how do I keep it in a new table, do you know? I.e. say I didn't want to add it to the GLBALANCEMASTER but to a new table called CALCULATIONS.

I gather my code at the moment will append the data to GLBALANCEMASTER? 

Thank you for your help!

[Brett_Bleess]

Ahmed, be sure to use the Accept as Solution button on the posts that helped you get things working, and I believe the last piece you needed is related to automatic concatenation, which is covered in the following Help link along with how to avoid it, so hopefully that will get you everything you needed.

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/LoadData/concat...

Regards,
Brett

[Ahmed_Turnaround]

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