topic Re: Add calculated rows in script in QlikView

Add calculated rows in script

cata_pli — Tue, 13 Jun 2017 08:53:49 GMT

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.

Re: Add calculated rows in script

Anil_Babu_Samineni — Tue, 13 Jun 2017 10:05:27 GMT

Could be possible, Would you able to provide sample data

Re: Add calculated rows in script

cata_pli — Tue, 13 Jun 2017 11:51:13 GMT

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.

Re: Add calculated rows in script

MarcoWedel — Tue, 13 Jun 2017 23:15:56 GMT

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

Re: Add calculated rows in script

cata_pli — Wed, 14 Jun 2017 06:09:53 GMT

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.