Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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;
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.
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 ;
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?
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;
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;