topic Re: three fields in one in QlikView

three fields in one

pgalvezt — Fri, 24 Aug 2012 15:25:15 GMT

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.

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 15:34:31 GMT

Sorry I forgot put the third table.

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 16:35:03 GMT

Any Help?

Thanks

Re: three fields in one

bgerchikov — Fri, 24 Aug 2012 17:17:08 GMT

Hey,

Why wouldn't you just left join ComercioCartera to Castigo and then delete it?

BTW, your QVW doesn't have any data and doesn't show data model.

Thanks!

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 17:30:45 GMT

I did this but the values changed:

Castigo:
Left Join(ComercioCartera)
LOAD
CC_CLI as CodComercio,
Deuda_Castigo,
Periodo
FROM

(qvd);

Drop Table ComercioCartera;

I took Concatenate From RecCastigo.

Any Help?

Re: three fields in one

bgerchikov — Fri, 24 Aug 2012 17:45:30 GMT

I think your Left join statement is wrong. Try the following:

Castigo:

LOAD

CC_CLI,

Deuda_Castigo,

Periodo

FROM

(qvd);

Left Join(Castigo) LOAD * Resident ComercioCartera;

Drop Table ComercioCartera;

rename CC_CLI to CodComercio;

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 18:37:41 GMT

3 Synthetic keys appears and the result was the same.

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 18:57:57 GMT

When I do clic in Cod Comercio works but Cod Comercio (Into ComercioCartera) Doesn't work.

Re: three fields in one

bgerchikov — Fri, 24 Aug 2012 19:00:47 GMT

I did it without the data, but hope it's gonna work:

Cartera:

LOAD CodComercio&"-"&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&"-"&Periodo as Key,

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;

DROP Tables Recuperacion_Castigo,ComercioCartera;

LinkTable:

NoConcatenate LOAD

Key,

CodComercio,

Periodo

Resident Castigo;

Concatenate LOAD

Key,

CodComercio,

Periodo

Resident Cartera;

Drop Fields CodComercio,Periodo from Castigo, Cartera;

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 19:27:11 GMT

LOAD CodComercio&"-"&Periodo as Key,

CodComercio is not a field into the cartera (Bases Tarjetas) So qlikview falling out

Re: three fields in one

bgerchikov — Fri, 24 Aug 2012 19:36:38 GMT

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;

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 19:52:45 GMT

Thank you but still gives error.

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 19:59:54 GMT

I took off

Recuperacion_Castigo:

Concatenate(Castigo)

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

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 20:00:21 GMT

I took off

Recuperacion_Castigo:

Concatenate(Castigo)

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

Re: three fields in one

bgerchikov — Fri, 24 Aug 2012 20:02:59 GMT

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;

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 20:13:28 GMT

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

Re: three fields in one

bgerchikov — Fri, 24 Aug 2012 20:34:54 GMT

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;

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 20:44:41 GMT

Works Until Exit Script;

Re: three fields in one

bgerchikov — Fri, 24 Aug 2012 20:47:31 GMT

Log file?

Re: three fields in one

pgalvezt — Fri, 24 Aug 2012 20:51:46 GMT

Log File