Calculation in script based on a field but hold calculation against a new ID

[Ahmed_Turnaround]

Hi all, 

Wondering if someone can help me with this issue that I have. I have the following code:

NOCONCATENATE GLBALANCE_R:
LOAD [GL BAL KEY], 
     [BALANCE FILE SUMMARY LEVEL], 
     [GL COMPANY ID], 
     [PROFIT CENTRE ID], 
     [GL ACCOUNT MAJOR ID], 
     [PROFIT CENTRE ID] + [GL ACCOUNT MAJOR ID] as PFAM_L,
     [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]
Resident GLBalance;

DROP TABLE GLBalance;

What needs to happen is the following:

IF [GL COMPANY ID] = 01, 
Hold the variable [Opening Balance For Year] somewhere. 
IF [GL COMPANY ID] is 02,
Hold the variable [Opening Balance For Year] elsewhere.
Add these up together in a new field called [Opening Balance For Year Combined]

I also need to hold this data gainst a new [GL COMPANY ID] for example CO for Combined.

Repeat the same process above for [GL MOVEMENT BASE] and [CLOSING BALANCE BASE] and hold the new figures against the new [GL COMPANY ID] so that I can bring the data into a Pivot Chart.

The confusing part is that the resident table holds the values for both [GL COMPANY ID] 01 and 02. I want to keep all calculations within the script.

Thanks in advance for any help. I can attach the QVW application somehow if required.

[sunny_talwar]

I think it might be easy to resolve this if you are able to share a qvw file.

Best,
Sunny

[Ahmed_Turnaround]

Hi Sunny,

Thank you for your reply. Attached is an example. I can't send the whole application as there is sensitive data but this holds the data table in my previous reply and the inline table I have created for the 01/02/03. 

Thanks for any help. 

[sunny_talwar]

This is sample is good, but can you explain what is not as per your expectation? Is this something on the front end which doesn't look right?

[Ahmed_Turnaround]

Hi Sunny,

Thank you for your reply. 

So, as you can see, the company ID's 01 and 02 have their respective opening, movement and closing. What needs to happen is have a combined figure and show that in it's own section in the pivot table. So there will be 03 or combined which will be the total opening/movement/closing. I'm not sure how to achieve this with an IF statement in the Expression. 

OR 

Have this calculation done in the script and hold it under the 03 company ID, so that I can then add it into the pivot table.

As you will see, if you go into the pivot chart properties and look at the expressions, I selected the Opening/Movement/Closing as an expression, so that it appears in the desired way in our pivot table. I basically want to have 3 separate showings of Opening/Movement/Closing, but the 3rd showing will be the totals of 01 and 02.

Ideally, I'd like the calculations done in the script as I believe this will be easier/quicker? 

Does this make sense with what I am trying to achieve?

Thank you

[sunny_talwar · Accepted solution]

Is there a reason you don't use Sum([OPENING BALANCE FOR YEAR]) instead of [OPENING BALANCE FOR YEAR]... because if you use Sum([OPENING BALANCE FOR YEAR]), you can enable total to see this

[Ahmed_Turnaround]

Hi Sunny,

Thank you for your reply. That's a good point, I didn't think of that. Let me try it in my main application with the sensitive data and I will get back to you. 

Bear with me. 

Thanks

[Ahmed_Turnaround]

Hi Sunny,

Looks like your method worked. I have checked it and seems to be correct. Thank you very much for your assistance. I will raise another help case if required but great stuff.

Thanks,