4 Replies Latest reply: Jul 30, 2015 8:16 PM by Rodrigo Restepo RSS

    row and column data changed?

      Please help me,

       

      I need to change the date row data column must be grouped code values for months,

       

      Example:I have this table

        

      DateÍtemQuantity
      01-ene12310
      10-ene12315
      15-ene1005
      25-ene10020
      30-ene20010
      02-feb12330
      10-feb20015
      18-feb20010
      24-feb2005
      27-feb10010

       

      but I need this

       

      ÍtemEneroFebrero
      1002510
      1232530
      2001030

       

      Thank you very much

        • Re: row and column data changed?
          Sunny Talwar

          Try this script:

           

          Table:

          LOAD * Inline [

          Date, Item, Quantity

          01-Jan, 123, 10

          10-Jan, 123, 15

          15-Jan, 100, 5

          25-Jan, 100, 20

          30-Jan, 200, 10

          02-Feb, 123, 30

          10-Feb, 200, 15

          18-Feb, 200, 10

          24-Feb, 200, 5

          27-Feb, 100, 10

          ];

           

          TempTable:

          LOAD Item,

            Sum(Quantity) as Jan

          Resident Table

          Where Month(Date#(Date, 'DD-MMM')) = 'Jan'

          Group By Item;

           

          Join (TempTable)

          LOAD Item,

            Sum(Quantity) as Feb

          Resident Table

          Where Month(Date#(Date, 'DD-MMM')) = 'Feb'

          Group By Item;

           

          DROP Table Table;

           

          Output in a table box object:

           

          Capture.PNG

            • Re: row and column data changed?

              Thank you very much

               

              But,I have a table in the database and I have this SQL statement, but this bad, that's the idea ,

              I do not know how to do it

               

              Please help me)

               

              data:
              LOAD *;
              SQL
              SELECT DATE,ITEM,SUM(QUANTITY) AS "ENE"
              FROM PDDTAEPM.DATA
              WHERE (DATE BETWEEN 115001 AND 115031) //DATE JULIANA
              GROUP BY ITEM;
               
              STORE data INTO datasum.qvd(qvd);

              DROP table data;  

            • Re: row and column data changed?
              Michael Tarallo

              Hi Rodrigo,

               

              if you are using Qlik Sense, please see attached .qvf example.

               

              There is an easier way to do this and it will support all the months you have in your data. I used a single wildmatch expression and a pivot table chart object.

               

              Here is the script: (note I am using this discussion thread as a data source using the web file connector in Qlik Sense)

               

              LOAD [Date],
                  [Ítem],
                  [Quantity]
                 
              FROM [lib://Rodrigo]
              (html, codepage is 1252, embedded labels, table is @1);
              

               

               

              In the master items I used:

               

              if(wildmatch([Date],'*-ene*'),'ene','feb')

               

               

              The used a Pivot Table object:

               

               

               

               

              Please copy .qvf file to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh Qlik Sense Desktop with F5

               

              Let us know how you do.

               

              Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

               

              Regards,

              Mike Tarallo

              Qlik

              • Re: row and column data changed?
                Andy Weir

                This method will create you the month column from your data without hardcoding the month names.

                 

                This assumes your month identifiers are 3 chars in length.

                 

                Note you could do this during the data load or as I have done here created a Master dimension I can use later in other charts.