5 Replies Latest reply: Mar 1, 2018 2:44 PM by Carlos Pulgarin RSS

    Help with a pivot table

    Carlos Pulgarin

      Hello, I have one question

       

      I have this table:

       

      id      Teléfono

      1          a    

      1          b    

      1          c

      2          e

      2          f

      3          g

      3          h

       

      An need the table like this:

       

      id      telefono1 telefono2 telefono3

      1               a          b               c

      2               e          f

      3               g          h

       

       

      I tried this:

       

      temp1:

      generic load * resident original;

       

       

      result:

      load distinct identificacion resident original;

       

       

      drop table original;

       

       

       

       

      FOR i = 0 to NoOfTables()

      TableList:

      LOAD TableName($(i)) as Tablename AUTOGENERATE 1

      WHERE WildMatch(TableName($(i)), 'temp1.*');

      NEXT i

       

       

      FOR i = 1 to FieldValueCount('Tablename')

      LET vTable = FieldValue('Tablename', $(i));

      LEFT JOIN (result) LOAD * RESIDENT $(vTable);

      DROP TABLE $(vTable);

      NEXT i

       

       

      drop table TableList;

       

      but it is too slow i have arround 800.000 telephones..

        • Re: Help with a pivot table
          John O'Brien

          You could calculate the values of telefono1, telefono2, telefono3 (another dimension) in the load script with something like this:

          NewTable:

          Load

               id,

               If( id = Peek( id ), 1, Peek( telefono_seq ) + 1 ) as telefono_seq

               Teléfono

          Resident Original

          Order By

               id asc ;


          I hope that makes sense.

            • Re: Help with a pivot table
              Bill Markham

              Or maybe something like this :

               

               

              Temp :

              LOAD * INLINE

              [

              id,Teléfono

              1,a

              1,b

              1,c

              2,e

              2,f

              3,g

              3,h

              ](delimiter is ',');

               

              Data :

              Load

              id ,

                  subfield ( cTeléfono , ',' , 1 )  as [Teléfono 1] ,

                  subfield ( cTeléfono , ',' , 2 )  as [Teléfono 2] ,

                  subfield ( cTeléfono , ',' , 3 )  as [Teléfono 3]

              ;

              Load

              id,

                  concat(Teléfono , ',') as cTeléfono

              Resident Temp

              group by id ;

               

              Drop table Temp ;

              • Re: Help with a pivot table
                Carlos Pulgarin

                Hello jhon, sorry this in my script

                 

                LIB CONNECT TO 'CRM PST';

                 

                 

                original:

                 

                 

                LOAD

                identificacion,

                numero;

                 

                original:

                SELECT "identificacion",

                "nombres",

                "apellidos",

                "numero",

                "fecha_telefono",

                "ciudad",

                "depto"

                FROM "public"."vw_telefonos_celular";

                 

                How will you do it?

                  • Re: Help with a pivot table
                    John O'Brien

                    Carlos,

                    Maybe I don't understand your question correctly.  Do you need a table in the data model with 4 fields called -

                    id, telefono1, telefono2, and telefono3?  Do you need a straight table or pivot table in the layout with those columns?


                    If you just need to show those columns in a pivot table, then the NewTable in my example above should work.

                     

                    If you need a table in the data model, then you could use some more load script like this (please pardon any typos):

                     

                    // create a new table that has the order of numero for each identificacion

                    NewTable:

                    Load

                         identificacion,

                         numero,

                         If( identificacion = Peek( identificacion ), 1, Peek( telefono_seq ) + 1 ) as telefono_seq

                    Resident original

                    Order By

                       id asc ;

                     

                    // transpose the rows of NewTable to columns

                    ResultTable:

                    Load Distinct

                         identificacion

                    Resident NewTable;

                    Left Join Load

                         identificacion,

                         numero as telefono1

                    Resident NewTable

                    Where

                         telefono_seq = 1;

                    Left Join Load

                         identificacion,

                         numero as telefono2

                    Resident NewTable

                    Where

                         telefono_seq = 2;

                    Left Join Load

                         identificacion,

                         numero as telefono3

                    Resident NewTable

                    Where

                         telefono_seq = 3;


                    Drop Table NewTable;

                      • Re: Help with a pivot table
                        Carlos Pulgarin

                        Thanks a lot,

                         

                        I did this, with your advices

                         

                        LIB CONNECT TO 'CRM PST';

                         

                         

                        Celulares:

                         

                         

                        LOAD

                        RowNo() as id,

                             trim(identificacion)&'-'&trim(numero) as numero;

                        //      if(identificacion='',null(),identificacion) as identificacion,

                        //      if(identificacion='',null(),numero) as numero;

                         

                         

                        Celulares:

                        SELECT "identificacion",

                        "nombres",

                        "apellidos",

                        "numero",

                        "fecha_telefono",

                        "ciudad",

                        "depto"

                        FROM "public"."vw_telefonos_celular";

                         

                         

                        Store Celulares into [lib://11.14/Telefonos/PST/Celulares.qvd](qvd);drop table Celulares;

                         

                         

                        Celulares:

                        LOAD

                            if(subfield(numero,'-',1)='',null(),subfield(numero,'-',1)) as identificacion,

                            if(subfield(numero,'-',1)='',null(),subfield(numero,'-',2)) as numero

                           

                        FROM [lib://11.14/Telefonos\PST\Celulares.qvd]

                        (qvd);

                         

                         

                        Store Celulares into [lib://11.14/Telefonos/PST/Celulares.qvd](qvd);drop table Celulares;

                         

                         

                         

                         

                        Temp:

                         

                         

                        Load  *

                         

                         

                        from [lib://11.14/Telefonos\PST\Celulares.qvd]

                        (qvd);

                         

                         

                        Celulares:

                         

                         

                        Load  *,1 as Flag

                         

                         

                        Resident Temp Order By identificacion;

                         

                         

                        Store Celulares into [lib://11.14/Telefonos/PST/Celulares.qvd](qvd);drop table Celulares;

                        Drop table Temp;

                         

                         

                         

                         

                        LOAD

                        RowNo() as id,

                            identificacion,

                            numero,

                            if(identificacion=Peek(identificacion,-8),9,

                            if(identificacion=Peek(identificacion,-7),8,

                            if(identificacion=Peek(identificacion,-6),7,

                            if(identificacion=Peek(identificacion,-5),6,

                            if(identificacion=Peek(identificacion,-4),5,

                            if(identificacion=Peek(identificacion,-3),4,

                            if(identificacion=Peek(identificacion,-2),3,

                            if(identificacion=Peek(identificacion,-1),2,1)))))))) as validacion

                            //If( identificacion = Peek( identificacion ), 1, Peek( telefono_seq ) + 1 ) as telefono_seq

                        FROM [lib://11.14/Telefonos\PST\Celulares.qvd]

                        (qvd);

                         

                         

                        Store Celulares into [lib://11.14/Telefonos/PST/Celulares.qvd](qvd);drop table Celulares;

                         

                         

                        NewTable:

                        LOAD

                            identificacion,

                            numero,

                            validacion

                        FROM [lib://11.14/Telefonos\PST\Celulares.qvd]

                        (qvd);

                         

                         

                         

                         

                        Final:

                         

                         

                        LOAD

                         

                            Distinct identificacion Resident NewTable;

                         

                         

                        Left Join Load

                            identificacion,

                            numero as Celular1 Resident NewTable Where validacion = 1;

                         

                         

                        Left Join Load

                            identificacion,

                            numero as Celular2 Resident NewTable Where validacion = 2;

                         

                         

                        Left Join Load

                        identificacion,

                            numero as Celular3 Resident NewTable Where validacion = 3;

                         

                         

                        Drop Table NewTable;