Add calculated rows in script

[cata_pli]

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)
A	N1	D1
B	N2	D1
C	N3	D1
A	N4	D2
B	N5	D2
C	N6	D2

F1javascript:; (Dimension)	F2 (Measure)	F3 (Date)
A	N1	D1
B	N2	D1
C	N3	D1
D	N1+N2	D1
E	N1+N3	D1
F	N1+N2+N3	D1
A	N4	D2
B	N5	D2
C	N6	D2
D	N4+N5	D2
E	N4+N6	D2
F	N4+N5+N6	D2

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.

[Anil_Babu_Samineni]

Could be possible, Would you able to provide sample data

[cata_pli]

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)

Thank you for your time.

[MarcoWedel · Accepted solution]

Hi,

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

table1:
LOAD Date,
    F1,
    Actual
FROM [https://community.qlik.com/servlet/JiveServlet/download/1286541-282852/TestData.xlsx] (ooxml, embedded labels, table is Sheet1);

tabTemp:
Generic LOAD * Resident table1;

tabTemp2:
LOAD 1 AutoGenerate 0;

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)
LOAD Date,
    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

[cata_pli]

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.