Help with a pivot table

[carlospulgarinz]

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..

[john_obrien]

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.

[Anonymous]

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 ;

[carlospulgarinz]

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?

[john_obrien · Accepted solution]

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;

[carlospulgarinz]

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;