Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
LOAD delegacion,
empresa,
almacen,
tipo,
Cantidad,
if(tipo = 'E' and Cantidad > 0 and tipoTienda = 'T', Cantidad, 0) as CantidadE
// 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
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
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
Thank you very much Miguel Angel