4 Replies Latest reply: Nov 11, 2013 5:09 PM by omar limon RSS

    Cross table

      Hi everyone;

      I charge data BUT when I use "Crosstable" I don't now why is not correct the charge for example

       

      In the file Excel called "acumulados" I filter the information by column "INFORMACION" and Choose:

      ACREEDORES, COSTO INTEGRAL,CXC,CXP, DEUDORES, GASTO VENTA. and  then I sum all columns for all months the columns to SUM is 201301,201302...201312

       

      the result is = 33797698

      and this is correct...BUT!!!!

      sumatotal.jpg

       

       

       

      AFTER I Charge the data from excel file apply Crosstable and the column INFORMATION apply

      IF(INFORMACION='VENTA',201,IF(INFORMACION='COSTO',57,IF( INFORMACION='GASTO DIRECCION','XXX',IF(INFORMACION='GASTO ADMON',  'YYY',IF (INFORMACION='COSTO VENTA','ZZZ','BALANCE' ) ) ) ) ) AS INFORMACION,

      and after I filter the information in the column called "INFORMACION"  again and Choose now: BALANCE and then sum the column  Data = 33761201

      and this is NOT  correct...

       

      BALANCE = sum of the ACREEDORES, COSTO INTEGRAL,CXC,CXP, DEUDORES, GASTO VENTA.

       

      Can you help please?

       

      CrossTable (test, Data, 3)
      LOAD
          
      IF(INFORMACION='VENTA',201,IF(INFORMACION='COSTO',57,IF( INFORMACION='GASTO DIRECCION','XXX',IF(INFORMACION='GASTO ADMON',  'YYY',IF (INFORMACION='COSTO VENTA','ZZZ','BALANCE' ) ) ) ) ) AS INFORMACION,
          
      VENDEDORA,
          
      COMPRADORA,
          
      [201301],
          
      [201302],
          
      [201303],
          
      [201304],
          
      [201305],
          
      [201306],
          
      [201307],
          
      [201308],
          
      [201309],
          
      [201310],
          
      [201311],
          
      [201312]
      FROM
      C:\Users\olimon\Documents\acumulados.xls
      (
      biff, embedded labels, table is [Sheet1$]);

       

        • Re: Cross table
          HECTOR GARCIA

          Omar I sugest you to do the transformations to your table after you have created the table with the crosstable,

          that way you can simplify your script.

           

           

           

          It is one step more but you can do it in a more structured way.

           

          espero haberte podido ayudar

           

          Hector

            • Re: Cross table

              I understand you what you mean is that FIRST charge the data in a resident table and after use the CorssTable function.  oohhh I gee but sorry dosen't  work in my originally .qvw I have this form what you say. but thanks anyway!!!!1 for your help!!!

               

              another idea?

              • Re: Cross table
                HECTOR GARCIA

                aplica este script y llegaras a este resultado:

                 

                data:

                CrossTable(Fechas, Datos, 3)

                LOAD INFORMACION,

                     VENDEDORA,

                     COMPRADORA,

                     [201301],

                     [201302],

                     [201303],

                     [201304],

                     [201305],

                     [201306],

                     [201307],

                     [201308],

                     [201309],

                     [201310],

                     [201311],

                     [201312]

                FROM

                acumulados.xls

                (biff, embedded labels, table is [Sheet1$]);

                 

                data1:

                Load

                *,

                IF(INFORMACION='VENTA',201,IF(INFORMACION='COSTO',57,IF( INFORMACION='GASTO DIRECCION','XXX',IF(INFORMACION='GASTO ADMON',  'YYY',IF (INFORMACION='COSTO VENTA','ZZZ','BALANCE' ) ) ) ) ) AS INFORMACION1

                resident data;

                 

                 

                drop table data;

                drop Field INFORMACION;

                rename field INFORMACION1 to INFORMACION;

                exit script;