topic Re: Calculation within Script failing in QlikView

Calculation within Script failing

Ahmed_Turnaround — Fri, 28 Feb 2020 11:19:28 GMT

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,

Re: Calculation within Script failing

m_woolf — Fri, 28 Feb 2020 15:27:10 GMT

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]

Re: Calculation within Script failing

Ahmed_Turnaround — Mon, 02 Mar 2020 09:00:54 GMT

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.

Re: Calculation within Script failing

Ahmed_Turnaround — Mon, 02 Mar 2020 09:30:13 GMT

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

Re: Calculation within Script failing

jonathandienst — Mon, 02 Mar 2020 11:04:35 GMT

I think you may have removed this too:

GROUP BY [SUB CATEGORY ID];

Add it back.

Re: Calculation within Script failing

Ahmed_Turnaround — Tue, 03 Mar 2020 15:26:34 GMT

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!

Re: Calculation within Script failing

Brett_Bleess — Wed, 01 Apr 2020 16:12:22 GMT

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/concatenate-tables.htm

Regards,
Brett

Re: Calculation within Script failing

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

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