topic Re: Without Synthetics Keys in QlikView

Without Synthetics Keys

pgalvezt — Fri, 20 Jul 2012 18:38:26 GMT

Hello! Im trying to load eight tables, all tables has the same fields so when I called with the same name appears Syntethics keys. So I did this: (see attached Files please (I took the synthetics keys). But when I load the qvd files some tables shows me values too much high. I think Im add more registers. can be? when I created my report (Pivot table) the total sum shows me in some tables good in others too high.

For Example:

Total excel Total QlikView (One Client)

954.855 954.855

Total Excel Total QlikView (Other Client)

345.325 12.954.855

Thanks.

Re: Without Synthetics Keys

cesaraccardi — Fri, 20 Jul 2012 18:49:34 GMT

Hi, sometimes QlikView loads empty lines from the excel files. Try to filter in the where clause of your load to prevent this. i.e.:

LOAD

B
FROM Excelfile.xls (...) WHERE not IsNull(A);

Regards,

Cesar

Re: Without Synthetics Keys

pgalvezt — Fri, 20 Jul 2012 18:52:45 GMT

Hi,

I loaded from dbf tables (Visual Fox Pro) Excel is used just for comparing results. See the attached please.

Thank you.

Re: Without Synthetics Keys

cesaraccardi — Fri, 20 Jul 2012 19:05:43 GMT

Ok, I'm not sure about the increased number of rows... But there is another thing you can use to avoid the synthetic keys, using the command QUALIFY *; before the loads make all fields to be renamed with the preffix <TABLENAME>.<field>

this is very helpful when you are building the data model.

You can use the UNQUALIFY field1, field2, ...; for the fields you want to have the same name.

Re: Without Synthetics Keys

pgalvezt — Fri, 20 Jul 2012 19:09:25 GMT

Could you please put an example with my qvw file.

Thanks.

Re: Without Synthetics Keys

cesaraccardi — Fri, 20 Jul 2012 19:13:29 GMT

Sure...

Re: Without Synthetics Keys

pgalvezt — Fri, 20 Jul 2012 19:24:52 GMT

I can see 2 tables. The idea is see one: I did this and I get it but the final result is not the same like in excel (Comparison)

BTCC1121:
OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=d:\modelos\solventa\tarjetas de credito\datos externos\bases_tc\2012\05_mayo\21 - cruz verde;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];
LOAD `cod_bloq`,`td_mora`,AutoNumber(cuenta&','& deudat&','& dvr&','& estado&','& ntarj&','& rut&','& `sdo_pro`&','&
`td_corte`&','& `tipo_cli`) as btcc11_21;
SQL SELECT *
FROM `btcc11_21`
Where estado = 1;

BTCC1140:
OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=d:\modelos\solventa\tarjetas de credito\datos externos\bases_tc\2012\05_mayo\40 - inp;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];
LOAD `cod_bloq`,`td_mora`,AutoNumber(cuenta&','& deudat&','& dvr&','& estado&','& ntarj&','& rut&','& `sdo_pro`&','&
`td_corte`&','& `tipo_cli`) as btcc11_40;
SQL SELECT *
FROM `btcc11_40`
Where estado = 1;

BasesUnion:
OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=d:\modelos\solventa\tarjetas de credito\datos externos\bases_tc\2012\05_mayo\21 - cruz verde;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];
Load AutoNumber(
cuenta&','& deudat&','& dvr&','& estado&','& ntarj&','& rut&','& `sdo_pro`&','&`td_corte`&','& `tipo_cli`)     as btcc11,
num(`cod_bloq`)                            as Cod_Bloqueo,
if(num(`cod_bloq`) <> 8, 'Vigente','Castigada')                    as CARTERATIPO,
left(cuenta,2)                             as Btcc11_Bin,
Num#(left(cuenta,2))&rut                          as Homologo_Btcc11,
deudat                              as Deuda_Total,
dvr                               as dvr,
estado                              as Estado,
ntarj                              as Num_Tarjeta,
rut                               as Rut,
rut&'-'&dvr                              as Rut_Dvr,
`sdo_pro`                             as DeudaK,
`td_corte`                             as TD_Corte,
`td_mora`                             as TD_Mora,
if(`tipo_cli` = 'R', 'Convenio', 'No Convenio')                    as Tipo_Cliente;
SQL SELECT *
FROM `btcc11_21`;

OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=d:\modelos\solventa\tarjetas de credito\datos externos\bases_tc\2012\05_mayo\40 - inp;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];
Concatenate LOAD AutoNumber(
cuenta&','& deudat&','& dvr&','& estado&','& ntarj&','& rut&','& `sdo_pro`&','&`td_corte`&','& `tipo_cli`)     as btcc11,
num(`cod_bloq`)                            as Cod_Bloqueo,
if(num(`cod_bloq`) <> 8, 'Vigente','Castigada')                    as CARTERATIPO,
left(cuenta,2)                             as Btcc11_Bin,
Num#(left(cuenta,2))&rut                          as Homologo_Btcc11,
deudat                              as Deuda_Total,
dvr                               as dvr,
estado                              as Estado,
ntarj                              as Num_Tarjeta,
rut                               as Rut,
rut&'-'&dvr                              as Rut_Dvr,
`sdo_pro`                             as DeudaK,
`td_corte`                             as TD_Corte,
`td_mora`                             as TD_Mora,
if(`tipo_cli` = 'R', 'Convenio', 'No Convenio')                    as Tipo_Cliente;
SQL SELECT *
FROM `btcc11_40`;

Store BasesUnion Into $(PathProdQvd)BasesUnionExample.qvd;
Drop table BTCC1121,BTCC1140;

Re: Without Synthetics Keys

cesaraccardi — Fri, 20 Jul 2012 19:39:08 GMT

I noticed you are filtering the field "estado = 1;" only in the first 2 loads.... isn't that the problem?

Re: Without Synthetics Keys

pgalvezt — Fri, 20 Jul 2012 20:13:38 GMT

Good point! I changed the others but the result is the same. wired because in my general report some values (Comparison) Excel - QlikView are perfect!! but others columns not. What can be?