Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

cata_pli
New Contributor II

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

F1javascript:; (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.

1 Solution

Accepted Solutions

Re: Add calculated rows in script

Hi,

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

QlikCommunity_Thread_263822_Pic1.JPG

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

4 Replies

Re: Add calculated rows in script

Could be possible, Would you able to provide sample data

cata_pli
New Contributor II

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)


Thank you for your time.


Re: Add calculated rows in script

Hi,

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

QlikCommunity_Thread_263822_Pic1.JPG

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
New Contributor II

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.