topic Re: script join load from different sources in QlikView

script join load from different sources

Tue, 04 Sep 2012 12:07:38 GMT

hi¡

The first sorry for my english.

This is my Script problem:

//First charge the 'TIENDAS' table (shop) from odbc

TIENDAS:

SQL SELECT

Almacen as almacen,

Empresa as empresa,

Delegacion as delegacion,

CASE WHEN A10_FRANQUICIA = 1

THEN 'F'

ELSE CASE WHEN Tipo='C'

THEN 'C'

ELSE 'T'

END

END AS tipoTienda,

FROM

RECIO.dbo.ocalm ;

//is OK. Second charge 'MOV' table (sales) from qvd file

MOV:

LOAD delegacion,

empresa,

almacen,

tipo,

articulo,

TipoDoc,

fecha,

Cantidad,

Precio

FROM

H:\QlikView\TablasAux\Movimientos.qvd (qvd);

//Third (and here I have the problem) I want to calculate a field depends of TIENDAS.tipoTienda and MOV.tipo at the same time

MOVPLUS:

LOAD delegacion,

empresa,

almacen,

tipo,

Cantidad,

if(tipo = 'E' and Cantidad > 0 and tipoTienda = 'T', Cantidad, 0) as CantidadE

Resident MOV;

// This is the error: tipoTienda is not know

DROP TABLE MOV;

The error is logical, but how I can join TIENDAS for 'tipoTienda' will know in MOVPLUS?

Thanks

Re: script join load from different sources

Miguel_Angel_Baeyens — Tue, 04 Sep 2012 13:00:17 GMT

Hi Emilio,

You can use the ApplyMap() function in this case as follows, to pass the fields that are common to both tables: delegacion, empresa and almacen and get the tipoTienda into the MOVPLUS table:

//First charge the 'TIENDAS' table (shop) from odbc

TIENDASMap:

MAPPING LOAD almacen & '/' & empresa & '/' & delegacion as key,

tipoTienda;

SQL SELECT

Almacen as almacen,

Empresa as empresa,

Delegacion as delegacion,

CASE WHEN A10_FRANQUICIA = 1

THEN 'F'

ELSE CASE WHEN Tipo='C'

THEN 'C'

ELSE 'T'

END

END AS tipoTienda,

FROM

RECIO.dbo.ocalm ;

//is OK. Second charge 'MOV' table (sales) from qvd file

MOV:

LOAD delegacion,

empresa,

almacen,

ApplyMap('TIENDASMap', almacen & '/' & empresa & '/' & delegacion) AS tipoTienda,

tipo,

articulo,

TipoDoc,

fecha,

Cantidad,

Precio,

if(tipo = 'E' and Cantidad > 0 and ApplyMap('TIENDASMap', almacen & '/' & empresa & '/' & delegacion) = 'T', Cantidad, 0) as CantidadE

FROM H:\QlikView\TablasAux\Movimientos.qvd (qvd);

Hope that gives you an idea.

Miguel

Re: script join load from different sources

Tue, 04 Sep 2012 13:19:36 GMT

Thank you very much Miguel Angel