Completar un registro con valores de otro registro ( lookup u otro ?)

[cim071865]

Dispongo de dos archivo excel: Facturas ( registro de facturas ) y Pagos (registro de los pagos de dichas facturas ). Ambos están relacionados a través del campo Doc.comp (une los pagos con las facturas ).

Facturas
Fe.valor	Importe en ML	Doc.comp.	Texto
05.06.2018	-102.630,76	1	SERVICIOS  S.A.
06.07.2018	-223.997,28	2	ADMINISTRACION FEDERAL DE INGRESOS
05.07.2018	-268,26	3	TELECOM ARGENTINA S.A.
25.07.2018	-316,42	3	BER Y PAST SACI
05.07.2018	-552,15	3	SIDER S.A.
02.05.2018	-588,06	3	GRAFICAS SRL
05.07.2018	-627,5	3	TELECOM ARGENTINA S.A.
05.07.2018	-8.026,20	4	TELEFONICA MOVILES ARGENTINA S.A
06.07.2018	-22.095,08	4	TELEFONICA MOVILES ARGENTINA S.A

Pagos
Fe.valor	Importe en ML	Doc.comp.	Texto
02.07.2018	102.630,76	1	CH. 48HS.22134823
06.07.2018	16.306,74	2	DEB.AUT.SE
06.07.2018	207.690,54	2	DEB.AUT.SE
06.07.2018	268,26	3	GO 0059122634
12.07.2018	316,42	3	GO 0059299280
06.07.2018	552,15	3	GO 0059120130
05.07.2018	588,06	3	GO 0059064049
06.07.2018	627,5	3	GO 0059122731
06.07.2018	30.121,28	4	MVSR

En el ejemplo adjunto cuatro casos :

Doc.comp. 1: una factura pagada con un cheque. ( igual monto factura y cheque )

Doc.comp. 2: una factura pagada con dos cheques. ( distinto monto factura y cheques pero la suma coincide )

Doc.comp. 3: varias facturas pagadas con varios cheques ( igual monto factura y cheque )

Doc.comp. 4: dos facturas pagadas con un solo cheque. ( distinto monto factura y cheques pero la suma coincide )

La fecha de vencimiento de las facturas están en el campo Fe.Valor. Las fecha de cobro del cheque también están en el campo Fe.Valor.

El nombre del proveedor de las facturas están en el campo Texto. El nro del cheque también esta en el campo Texto.

Necesito calcular el tiempo promedio de demora en cobrar el cheque, que se obtendría entre la fecha de vencimiento de la factura (campo Fe.Valor archivo Facturas ) y la fecha de cobro del cheque (campo Fe.Valor archivo Pagos ).

Para ello utilicé la función LOOKUP luego de bajar el archivo Facturas y antes de bajar el archivo de Pagos, de manera de agregar en este la fecha de vencimiento que está en la tabla de factura. En el caso del Doc.comp. 1, 2 y 4 obtengo lo que quiero, pero en el caso del Doc.comp. 4, como tiene varias facturas y cheques, no obtengo bien el dato xq me trae el dato del primer registro que encuentra.

Me podrían dar una solución distinta o decirme como mejorar la función lookup para solucionar este problema. Estos dos archivos ( Pagos y Facturas ) los puedo tener en uno solo.

Muchas gracias

[hector_munoz]

Hola Claudio,

El primer problema con el que te encuentras es de "proceso de negocio" ya que no tienes un identificador de factura único. Me explico, una factura se puede desglosar en varios importes que comparten un ID (Doc.comp.) pero luego se puede pagar una factura de varios importes con un único cheque y viceversa, una factura de un importe con varios cheques.

Entonces, ¿cuál sería el criterio?

• Para la factura 1 es sencillo: 1 importe y 1 cheque.
• Para la factura 2 es sencillo: 1 importe y 2 cheques con la misma fecha.
• Para la factura 3 es más complicado: 5 importes y 5 cheques por el mismo importe; ¿se cruzaría por Doc.comp. e importe con el signo cambiado?
• Para la factura 4 es más complicado:1 importe y 2 cheques con fecha distinta, ¿qué fecha tomar?

Se me ocurre que crees 2 tablas de mapeo (MAPPING LOAD):

• La primera, cargada a partir de la información de cheques, con una clave formada por el ID de la factura (Doc.comp.) + el importe con el signo cambiado, y con el segundo campo con la fecha de pago del cheque.
• La segunda, también cargada a partir de la información de cheques, con una clave igual al ID de la factura (Doc.comp.), y con el segundo campo con la fecha de pago del cheque.

Entonces, hecho esto, cuando cargues las facturas primero compruebas (con la función ApplyMap yéndote a la primera tabla de mapeo del paso anterior) si hay algún cheque que tenga la misma combinación de ID factura (Doc.comp.) + importe. En caso afirmativo recuperarías la fecha de pago de la factura con el cheque del mismo importe que la factura. En caso negativo compruebas (con la función ApplyMap yéndote a la segunda tabla de mapeo del paso anterior) si hay algún cheque que tenga la misma ID factura (Doc.comp.). En caso afirmativo recuperarías la fecha de pago de la factura con el cheque. En caso negativo la factura aún no se ha pagado y yo pondría la fecha de la recarga con la función Today(1) y crearía un campo de flag para indicar que aún no se ha pagado. Ya teniendo la fecha de pago del cheque restaría a esta la de vencimiento de la factura.

No sé si me he explicado bien... Espero que te sirva...

Saludos,

H

[cim071865]

Hola Héctor, muchas gracias por la respuesta.

Soy muy nuevo en esto por lo cual, junto a algunos compañeros que me están ayudando, estamos probando la solución que me sugerís. Aún no he podido llevarla a cabo porque estamos estudiando algo más sobre el uso del Mapping y Aplymap para obtener la solución propuesta.

En cuanto pueda hacerlo te comento como me fué.

Muchos saludos

[cim071865]

Hola Héctor, estoy teniendo problemas cuando anido en ApplyMap las dos tablas de mapeo que me sugeris. Se pueden anidar la función ApplyMap ? en ese caso me está faltado/sobrando algo ??

Saludos

[Mov E]: // Bajo la tabla de cheques

LOAD

[Clase],

Date(Date#([Fe.pago], 'MM.DD.YYYY') ) AS [Fe.pago],

[Importe en ML],

[Doc.comp.],

[Texto]

FROM [lib://AttachedFiles/Detalle Cheques.xlsx]

(ooxml, embedded labels, header is 2 lines, table is [Mov E]);

[Map_1]:

Mapping Load

    ([Importe en ML]*-1)&[Doc.comp.] as ID_Map_1 ,

[Fe.pago]

Resident [Mov E];

[Map_2]:

Mapping Load

    [Doc.comp.]as ID_Map_2 ,

[Fe.pago]

Resident [Mov E];

[Mov Z]: //Bajo la tabla de Facturas

LOAD

ApplyMap ( 'Map_1',    [Importe en ML]&[Doc.comp.],

      ApplyMap( '[Map_2]',

              [Doc.comp.])) as FECHA_PAGO,

[Clase] AS [Mov Z.Clase],

Date(Date#([Fe.Vencim.], 'MM.DD.YYYY') ) AS [Fe.Vencim.],

[Importe en ML] AS [Mov Z.Importe en ML],

[Doc.comp.] AS [Mov Z.Doc..comp..],

[Texto] AS [Mov Z.Texto]

FROM [lib://AttachedFiles/Detalle Cheques.xlsx]

(ooxml, embedded labels, header is 2 lines, table is [Mov Z]);

[cim071865]

Hola Héctor, quería comentarte que tu solución me ayudó muchísimo, ya pude probarla después de haber intentado varias veces que funcione ApplyMap. Es que soy nuevo y cada paso me lleva algún tiempo.

Si bien tengo un problema de "proceso de negocio", que debo solucionar , tu sugerencia funcionó bien para la mayoría de las transacciones.

Nuevamente te agradezco y me alegra saber que cuento con personas como vos dispuestas a ayudar.

Saludos

[hector_munoz]

Me alegra Caludio y disculpa por no mirar más este hilo; he estado un poco desconectado últimamente... Saludos y gracias a ti!!!

[cim071865]

Hola Héctor, disculpá la molestia pero tal vez, sin hacerte perder tiempo, me podés solucionar un problema que estoy teniendo o me das una idea por donde investigar.

Recién ahora me doy cuenta que la rutina que me sugeriste  no funciona para los número que no tiene decimales, si para el resto que son la mayoría.

Funciona en todos los casos de importes de facturas y cheques que coinciden exactamente y tienen decimales. No funciona para todos los casos de números que coinciden pero no tienen decimales.  Es muy raro y no tengo idea por donde puedo buscar.

No se tiene algo que ver pero te adjunto la regionalización:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

.....

Saludos