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

    Add calculated rows in script

    Catalin Plitea

      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.

        • Re: Add calculated rows in script
          Anil Babu Samineni

          Could be possible, Would you able to provide sample data

            • Re: Add calculated rows in script
              Catalin Plitea

              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
              Marco Wedel

              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

                • Re: Add calculated rows in script
                  Catalin Plitea

                  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.