4 Replies Latest reply: Jun 14, 2017 2:09 AM by Catalin Plitea

# Add calculated rows in script

Hello,

Is it possible to transform the first table into the second table in the data load script ?

My real data has more existing rows, dates, and new calculated rows needed, but logically is the same problem, to add calculated rows to every date.

My real data has about 36 different dates (MMM-YY), 50 distinct F1 values, and about 10 new values needed for each date.

F1 (Dimension)F2 (Measure)F3 (Date)
AN1D1
BN2D1
CN3D1
AN4D2
BN5D2
CN6D2

F1 (Dimension)

F2 (Measure)F3 (Date)
AN1D1
BN2D1
CN3D1
DN1+N2D1
EN1+N3D1
FN1+N2+N3D1
AN4D2
BN5D2
CN6D2
DN4+N5D2
EN4+N6D2
FN4+N5+N6D2

I have tried using set analysis in the objects, but I end up with very long statements.

Is it possible to do it in the script ?

Thank you.

• ###### Re: Add calculated rows in script

Could be possible, Would you able to provide sample data

• ###### Re: Add calculated rows in script

I have attached some data.

Sheet 1 shows given sample data.

I want for every date to insert 4 new rows in the table, which are:

J=C+F+G+H+I

K=(C+F+G+H+I) / A, which is the same with K= J / A

L=B+D+E

M=(B+D+E) / A, which is the same with  M = L / A

Sheet 2 shows how I would like the resulting table to look like. (I made manual calculation only for first 2 months)

• ###### Re: Add calculated rows in script

Hi,

maybe one solution to use your existing formulas might be something like:

```table1:
F1,
Actual

tabTemp:

tabTemp2:

FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName(\$(i));
IF WildMatch('\$(vTable)', 'tabTemp.*') THEN
JOIN (tabTemp2) LOAD * RESIDENT [\$(vTable)];
DROP TABLE [\$(vTable)];
ENDIF
NEXT i

CrossTable (F1, Actual)
C+F+G+H+I      as J,
(C+F+G+H+I) / A as K,
B+D+E          as L,
(B+D+E) / A    as M
Resident tabTemp2;

DROP Table tabTemp2;
```

hope this helps

regards

Marco

• ###### Re: Add calculated rows in script

That is absolutely beautiful Marco. You are a real legend

My data has another column (budget), but I was able to squeeze that in by applying your solution twice and then joining the two tables.

I will now apply it to my real data, which has more information and calculations, but I think it will work.

Thank you.