Unqualified fields not working as key

[#1]

Hi everyone, I've this issue with keys that dont match when it should...!

I've several tables related throught the field date in its separated form: year, month, quarter and others. The problem is that with only a table, the relation is not working and when I create a table with all the fields including the unqualified one: year, it appears -, representing null.

eg:

as you can see, the field named anno is the one it supouse to work as key with the rest of the model; anno_copy it's the same field but unqualified. So, how and why come that the field anno appears as null in all the rows if it real value is 2010..??? why is not taking the relation?

I appreciate in advance your response! Regards!

[pover]

Please post your script.

[#3]

Hi Karl, I've attached my script for you to check it.

Thxs in advance!

[pover]

Leonor,

Is that all your script or do you join or concatenate the tables later on in the script? If that is all your script then you are creating multilple synthetic table and that is probably the cause of your problem. I also see that you've commented in the script that tables are null, too.

PAGOS.anno_copy returns a year because it is not a key like anno so I would recommend you use the a link table (look in forum) or better yet remove the qualify statement and use concatenate to combine the tables in one large fact table. Start by combine 2 tables, then 3 tables, then 4 tables, etc. until you have a good data model.

Regards.

[#5]

Hi Karl,

Certainly, that's all in my script. I'll try what u recommend me about concatenate tables :s

I hope to get my problem solve.

Thxs and I'll writing as son as I can.

[#6]

Hello lenonor,

Unqualified fields are working as a KEY , i have used it many time.

Their might be some error in your script.

[#7]

QUARTERS_MAP:
UNQUALIFY *;
MAPPING LOAD
rowno() AS MONTH,
'T' & CEIL(rowno()/3) AS QUARTER
AUTOGENERATE(12);

you have used unqualify with any qualify statement over here.

Also you should unqualify after each table to whom you have qualified.

Satement will be :

Table name :

Qualify * ;

unqualify ' filed_name' ;

load A

B

FROM
DATA\GERENCIAS.QVD
(qvd);

unqualify *;

This will work all correct.

let me know if again there is any problem

[#8]

Here is your CODE !!

Use Qualify and Unqualify in this way .................

///$tab MAESTROS
PROVEEDORES:
QUALIFY *;
UNQUALIFY co_enterprise, Proveedor;
Directory;
LOAD co_enterprise,
tipo_prov,
tipo_per,
co_prov,
Proveedor
FROM
DATA\PROVEEDORES.QVD
(qvd);

UNQUALIFY * ;

GERENCIAS:
QUALIFY *;
UNQUALIFY co_enterprise, Gerencia;
Directory;
LOAD co_enterprise,
id_gerencia,
Gerencia
FROM
DATA\GERENCIAS.QVD
(qvd);

UNQUALIFY * ;



PROYECTOS:
QUALIFY *;
UNQUALIFY co_enterprise, Gerencia, anno, mes, dia, trimestre, Proyecto;
//---> AQUI se nuleo el año
Directory;
LOAD co_enterprise,
id_proyecto,
Proyecto,
id_gerencia,
Gerencia,
id_anio,
propio,
fecha,
year(fecha) as anno,
// anno_ejec as anno,
mes_ejec as mes,
dia_ejec as dia,
trimestre_ejec as trimestre,
id_destino,
Gerencia_Destino,
id_estatus
FROM
DATA\PROYECTOS.QVD
(qvd);

UNQUALIFY * ;


PARTIDAS:
QUALIFY *;
UNQUALIFY co_enterprise, Proyecto, Partida, Gerencia;
Directory;
LOAD co_enterprise,
id_partida,
Partida,
id_proyecto,
Proyecto,
propio,
id_destino,
Gerencia_Destino,
id_estatus,
id_gerencia,
Gerencia
FROM
DATA\PARTIDAS.QVD
(qvd);

UNQUALIFY * ;

[#9]

Hi Karl,

I just concatenate PAGOS table with DOCUMENTOS_COMPRA, that store similar documents and the null keeps apearing in the year field.

So, when I select 2010, dont show the amount it should in pagos.

Here's the code of my concatenation for you to check it:

DOCUMENTOS_COMPRA:
QUALIFY *;
UNQUALIFY co_enterprise, anno, mes, trimestre, Proveedor, Proyecto, Partida, Subpartida, Gerencia;
Directory;
LOAD co_enterprise,
tipo_doc,
if(tipo_doc='OP', fecha_pago, fec_emis) as fecha,
// if(tipo_doc='OP', anno_pago, anno_emis) as anno,
 if(tipo_doc='OP', year(fecha_pago), year(fec_emis)) as anno,
if(tipo_doc='OP', mes_pago, mes_emis) as mes,
// if(tipo_doc='OP', dia_pago, dia_emis) as dia,
 if(tipo_doc='OP', trimestre_pago, trimestre_emis) as trimestre,
nro_doc,
fec_emis,
anno_emis,
mes_emis,
// dia_emis,
 trimestre_emis,
descripcion,
co_prov,
monto,
saldo,
id_proyecto,
id_partida,
id_subpartida,
nro_reng,
fecha_pago,
anno_pago,
mes_pago,
dia_pago,
trimestre_pago,
fecha_req,
anno_req,
mes_req,
// dia_req,
 trimestre_req,
num_org,
reng_org,
doc_org,
fecha_recibido,
anno_recibido,
mes_recibido,
// dia_recibido,
 id_gerencia,
Proyecto,
Proveedor,
Partida,
Subpartida,
Gerencia,
OC_codcomp,
OC_codcomp AS OC_codcomp_LINK,
FechaEmis_OCorg,
AnnoEmis_OCorg,
MesEmis_OCorg,
// DiaEmis_OCorg,
 TrimestreEmis_OCorg
FROM
DATA\DOCUMENTOS_COMPRA.QVD
(qvd);



//PAGOS:
//QUALIFY *;
//UNQUALIFY co_enterprise, anno, mes, trimestre, Proveedor;
Directory;
concatenate LOAD co_enterprise,
cob_num,
tip_cob,
mont_doc,
fec_emis,
year(fec_emis) as anno,
year(fec_emis) as anno_copy,
// anno_emis as anno,
 mes_emis as mes,
// dia_emis as dia,
 trimestre_emis as trimestre,
co_prov,
Proveedor,
cuenta_caja,
banco_descripcion
FROM
DATA\VRENG_PAGOS.QVD
(qvd);

Thxs a lot in advance for your response! this's driving me crazy...

[#10]

Hi Ashwin,

I really dont understand how and why unqualifying after each table will make any change, specially because I qualify * before the load of each one.

Even though I tried it, and the problem persists.

Thxs for your response