Re: three fields in one - Page 2 - Qlik Community

pgalvezt · ‎2012-08-24

Hi, I trying to join 3 tables "Castigo" "RecCastigo" and "ComercioCartera" the field key is Cc_Cli (Customer Code) But this Cc_Cli in "Castigo" has another name and the same thing for "RecCastigo" so I concatenated "Castigo" and Rec.Castigo" and renamed into "Castigo" the field CC_CLI as CodComercio, When I Do clic in Cod Comercio "Castigo" and " RecCastigo" Show me the real values so works fine. But I have to join another table call "ComercioCartera" This table has a Cc_Cli too when I renamed like CodComercio (Reference Circular appears) When Tried with applymap and concatenate (appears Synthetics Keys) and the values change completely. Wht I want is to do clic in "CodComercio" or "Comercio" and QlikView shows the real values. I don't want to 2 "Comercio"

Please help with this Im really stuck with this issue.

Thank you.

bgerchikov · ‎2012-08-24

Sorry, I don't have data, so mistakes are very possible:

Just try to understand my idea.

Cartera:

LOAD Left(Clasificacion_Bin,2)&"-"&Periodo as Key,

     Periodo,

Estado,

     Cod_Bloqueo,

     Vigente_Castigo,

     Tipo_Cliente,

     Left(Clasificacion_Bin,2) as Clasificacion_Bin,

     'Tarjeta' as LineadeNegocio,

     'Casa Matriz' as Sucursal,

     DiasMora,

     Año,

     Mes,

     Deuda_Total,

     Num_Tarjeta_OP,

     DeudaK,

     Source

FROM

(qvd);

Castigo:

LOAD

CC_CLI as CodComercio,

Deuda_Castigo,

Periodo

FROM

(qvd);

Recuperacion_Castigo:

Concatenate(Castigo)

LOAD

//ApplyMap('MappingCastigos',CodComercio) as CodComercio,

CodComercio,

     Año_Bsc02,

     Periodo,

     MontoPago as Recuperacion_Castigo

FROM

(qvd);

ComercioCartera:

LOAD

     Clasificacion_Bin,

     CC_Cli as CodComercio,

     Comercio

FROM

(qvd);

Left Join (Castigo) LOAD * Resident Recuperacion_Castigo;

Left Join (Castigo) LOAD * Resident ComercioCartera;

TempCastigo:

NoConcatenate LOAD *,

Clasificacion_Bin&"-"&Periodo as Key

Resident Castigo;

DROP Tables Recuperacion_Castigo,ComercioCartera,Castigo;

RENAME Table TempCastigo to Castigo;

LinkTable:

NoConcatenate LOAD

Key,

Clasificacion_Bin,

Periodo

Resident Castigo;

Concatenate LOAD

Key,

Clasificacion_Bin,

Periodo

Resident Cartera;

Drop Fields Clasificacion_Bin,Periodo from Castigo, Cartera;

pgalvezt · ‎2012-08-24

Thank you but still gives error.

pgalvezt · ‎2012-08-24

I took off

Recuperacion_Castigo:

Concatenate(Castigo)

because qlikview doesn't found the table. But now appears "Out of Memory"

pgalvezt · ‎2012-08-24

I took off

Recuperacion_Castigo:

Concatenate(Castigo)

because qlikview doesn't found the table. But now appears "Out of Memory" and qlikview fails.

bgerchikov · ‎2012-08-24

One more fix:

Cartera:

LOAD Left(Clasificacion_Bin,2)&"-"&Periodo as Key,

     Periodo,

Estado,

     Cod_Bloqueo,

     Vigente_Castigo,

     Tipo_Cliente,

     Left(Clasificacion_Bin,2) as Clasificacion_Bin,

     'Tarjeta' as LineadeNegocio,

     'Casa Matriz' as Sucursal,

     DiasMora,

     Año,

     Mes,

     Deuda_Total,

     Num_Tarjeta_OP,

     DeudaK,

     Source

FROM

(qvd);

Castigo:

LOAD

CC_CLI as CodComercio,

Deuda_Castigo,

Periodo

FROM

(qvd);

//Recuperacion_Castigo:

Join(Castigo)

LOAD

//ApplyMap('MappingCastigos',CodComercio) as CodComercio,

CodComercio,

     Año_Bsc02,

     Periodo,

     MontoPago as Recuperacion_Castigo

FROM

(qvd);

//ComercioCartera:

Join(Castigo)

LOAD

     Clasificacion_Bin,

     CC_Cli as CodComercio,

     Comercio

FROM

(qvd);

TempCastigo:

NoConcatenate LOAD *,

Clasificacion_Bin&"-"&Periodo as Key

Resident Castigo;

DROP Table Castigo;

RENAME Table TempCastigo to Castigo;

LinkTable:

NoConcatenate LOAD

Key,

Clasificacion_Bin,

Periodo

Resident Castigo;

Concatenate LOAD

Key,

Clasificacion_Bin,

Periodo

Resident Cartera;

Drop Fields Clasificacion_Bin,Periodo from Castigo, Cartera;

pgalvezt · ‎2012-08-24

I attached the lof qlikview file. Appears "Out Of Memory"

bgerchikov · ‎2012-08-24

OK,

Try to load table by table...

I noticed it might be a problem with "-" in Key field. Replace it with '-'

Cartera:

LOAD Left(Clasificacion_Bin,2)&'-'&Periodo as Key,

     Periodo,

Estado,

     Cod_Bloqueo,

     Vigente_Castigo,

     Tipo_Cliente,

     Left(Clasificacion_Bin,2) as Clasificacion_Bin,

     'Tarjeta' as LineadeNegocio,

     'Casa Matriz' as Sucursal,

     DiasMora,

     Año,

     Mes,

     Deuda_Total,

     Num_Tarjeta_OP,

     DeudaK,

     Source

FROM

(qvd);

Castigo:

LOAD

CC_CLI as CodComercio,

Deuda_Castigo,

Periodo

FROM

(qvd);

//Recuperacion_Castigo:

Join(Castigo)

LOAD

//ApplyMap('MappingCastigos',CodComercio) as CodComercio,

CodComercio,

     Año_Bsc02,

     Periodo,

     MontoPago as Recuperacion_Castigo

FROM

(qvd);

//ComercioCartera:

Join(Castigo)

LOAD

     Clasificacion_Bin,

     CC_Cli as CodComercio,

     Comercio

FROM

(qvd);

TempCastigo:

NoConcatenate LOAD *,

Clasificacion_Bin&'-'&Periodo as Key

Resident Castigo;

DROP Table Castigo;

RENAME Table TempCastigo to Castigo;

LinkTable:

NoConcatenate LOAD

Key,

Clasificacion_Bin,

Periodo

Resident Castigo;

Join LOAD

Key,

Clasificacion_Bin,

Periodo

Resident Cartera;

Drop Fields Clasificacion_Bin,Periodo from Castigo, Cartera;

pgalvezt · ‎2012-08-24

Works Until Exit Script;

bgerchikov · ‎2012-08-24

Log file?

pgalvezt · ‎2012-08-24

Log File