script join load from different sources

[#1]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

Thank you very much Miguel Angel