topic Unqualified fields not working as key in QlikView

Unqualified fields not working as key

Wed, 12 Jan 2011 22:25:57 GMT

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!

Unqualified fields not working as key

pover — Wed, 12 Jan 2011 22:56:48 GMT

Please post your script.

Unqualified fields not working as key

Thu, 13 Jan 2011 15:15:59 GMT

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

Thxs in advance!

Unqualified fields not working as key

pover — Thu, 13 Jan 2011 19:04:19 GMT

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.

Unqualified fields not working as key

Thu, 13 Jan 2011 21:02:15 GMT

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.

Unqualified fields not working as key

Fri, 14 Jan 2011 08:35:52 GMT

Hello lenonor,

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

Their might be some error in your script.

Unqualified fields not working as key

Fri, 14 Jan 2011 08:41:03 GMT

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

FROM
DATA\GERENCIAS.QVD
(qvd);

unqualify *;

This will work all correct.

let me know if again there is any problem

Unqualified fields not working as key

Fri, 14 Jan 2011 08:44:46 GMT

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 * ;

Unqualified fields not working as key

Fri, 21 Jan 2011 16:53:19 GMT

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

Unqualified fields not working as key

Fri, 21 Jan 2011 17:03:37 GMT

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

Unqualified fields not working as key

pover — Sat, 22 Jan 2011 01:51:40 GMT

Leonor,

This sure seems crazy. To help to see what the problem is if you load only PAGOS do you see values in anno? Does anno_copy still give you values when you concatenate? Does the field VRENG_PAGOS.anno exist?

Try not using qualify and to indentify if a mont_doc is from PAGOS you should create an additional field like

'PAGOS' as [Document Type]

Regards.

Unqualified fields not working as key

Sun, 23 Jan 2011 03:21:58 GMT

Hi Karl, first of all, I'm very grateful for your disposition to help.

Secondly, I've check every line on the script and found out that the problem were when I created the QVD's (no way of you to see it). Wich was the problem? well, when doing the mapping for the field trimestre (quarter) of the table PAGOS, I missed a letter of the name map table, instead writing quarter_map I wrote quarter_ma.

I noticed because I started to unqualify each field at the time, and was with trimestre that all gone null.

I'm sorry for all the trouble and all the thoughts of: what the hell is happening here!?

Thxs for your responses

Regards