Qlik Community

Healthcare User Group

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
alejortizp
Contributor III
Contributor III

Trying to use intervalmatch with join

Hi,

Could anyone help please,

I want to make table join with a key and range of dates, but i don't know why it is duplicated the register.

 

[La_33]:
LOAD
NIT_IPS&'-'&COD_SERVICIO&'-'&TIPO_CONTRATO AS LLAVE1,
NIT_IPS&'-'&COD_SERVICIO&'-'&TIPO_CONTRATO AS LLAVE2,
NUM_FACTURA,
NUM_RADICA,
ESTADO_RADICA,
ITEM,
TIPO_DOC,
DOC_AFILIADO,
TIPO_DOC&'-'&DOC_AFILIADO AS DOCUMENTO,
COD_SEXO,
EDAD,
DIAGNOSTICO,
DESCR_DIAGNOSTICO,
DIA_RADICACION,
MES_RADICACION,
ANIO_RADICACION,
DIA_RADICACION&'/'&MES_RADICACION&'/'&ANIO_RADICACION as Fecha_Radicacion,
DIA_CONTABLE,
MES_CONTABLE,
ANIO_CONTABLE,
DIA_CONTABLE&'/'&MES_CONTABLE&'/'&ANIO_CONTABLE as Fecha_Contable,
DIA_PRESTACION,
MES_PRESTACION,
ANIO_PRESTACION,
// ANIO_PRESTACION as PERIODO_TARIFA,
DIA_PRESTACION&'/'&MES_PRESTACION&'/'&ANIO_PRESTACION as Fecha_Prestacion,
CCONTABLE,
DESC_CONCEPTO_CONTABLE,
VALOR_COBRADOIPS,
VALOR_APAGAR,
CANTIDAD,
VALOR_APAGAR/CANTIDAD as VALOR_UNITARIO,
RAZON_SOCIAL,
NIT_IPS AS NIT_IPS_INTEGRACION,
MUNICIPIO_AFI,
if(MUNICIPIO_AFI = 'MEDELLIN','MEDELLIN','RESTO') as Dist.Mun.AFI,
DEPARTAMENTO_AFI,
REGION_AFI,
MUNICIPIO_IPS,
if(MUNICIPIO_IPS='MEDELLIN','MEDELLIN','RESTO') as Dist.Mun.IPS,
DEPARTAMENTO_IPS,
REGION_IPS,
COD_SERVICIO AS C_SERVICIO,
if(index(COD_SERVICIO, '-', 1) > 0,
left(COD_SERVICIO,
index(COD_SERVICIO, '-', 1) - 1), COD_SERVICIO) as COD_SERVICIO_SIN_GUION,
COD_GRUPO,
COD_SUBGRUPO,
DESCR_SERVICIO,
COD_PATOLOGIA,
DESC_PATOLOGIA,
TIPO_CONTRATO AS TIPO_CONTRATO_33
// , TRIM(TIPO_CONTRATO) AS TIPO_CONTRATO_ANTIGUO,
// COD_SERVICIO as COD_SERVICIO_CONTRATO_ANTIGUO,
// NIT_IPS AS NIT_IPS_CONTRATO_ANTIGUO
FROM [lib://qvdsalud (integrasavia_svcqlik)/Contabilizacion_Contable.qvd]
(qvd);

NoConcatenate

[La_33_integracions]:
Load
LLAVE1,
LLAVE2,
NUM_FACTURA,
NUM_RADICA,
ESTADO_RADICA,
ITEM,
TIPO_DOC,
DOC_AFILIADO,
DOCUMENTO,
COD_SEXO,
EDAD,
DIAGNOSTICO,
DESCR_DIAGNOSTICO,
DIA_RADICACION,
MES_RADICACION,
ANIO_RADICACION,
DIA_CONTABLE,
MES_CONTABLE,
ANIO_CONTABLE,
DIA_PRESTACION,
MES_PRESTACION,
ANIO_PRESTACION,
CCONTABLE,
DESC_CONCEPTO_CONTABLE,
VALOR_COBRADOIPS,
VALOR_APAGAR,
CANTIDAD,
VALOR_APAGAR/CANTIDAD as VALOR_UNITARIO,
RAZON_SOCIAL,
NIT_IPS_INTEGRACION,
MUNICIPIO_AFI,
DATE(Date#(Fecha_Prestacion,'DD/MM/YYYY')) AS Fecha_Prestacion,
DATE(Date#(Fecha_Radicacion,'DD/MM/YYYY')) AS Fecha_Radicacion,
DATE(Date#(Fecha_Contable,'DD/MM/YYYY')) AS Fecha_Contable,
Dist.Mun.AFI,
DEPARTAMENTO_AFI,
REGION_AFI,
MUNICIPIO_IPS,
Dist.Mun.IPS,
DEPARTAMENTO_IPS,
REGION_IPS,
C_SERVICIO,
COD_SERVICIO_SIN_GUION,
COD_GRUPO,
COD_SUBGRUPO,
DESCR_SERVICIO,
COD_PATOLOGIA,
DESC_PATOLOGIA,
TIPO_CONTRATO_33
Resident La_33
Where ANIO_PRESTACION>2017 and (NIT_IPS_INTEGRACION='900625317' or NIT_IPS_INTEGRACION='890904646' or NIT_IPS_INTEGRACION='800058016');
Drop Table La_33;

CONTRATOS:
LOAD
NIT_IPS&'-'&COD_SERVICIO&'-'&IF(MODELO_CONTRATA='EVENTO','E','OTROS') AS LLAVE1,
COD_SERVICIO,
COD_SUBGRUPO as SUBGRUPO,
COD_GRUPO AS GRUPO,
DESCR_SERVICIO AS DESC_SERVICIO,
ESTADO_CONTRATO,
CONTRATO_ENTIDAD,
CONTRATO_LEGAL,
NIVEL_COMPLEJIDAD,
MODELO_CONTRATA,
IF(trim(MODELO_CONTRATA)='EVENTO','E','OTROS') AS TIPO_CONTRATO,
DATE(Date#(FECHAINICIO,'DD/MM/YYYY')) AS FECHAINICIO,
DATE(Date#(FECHAFINAL,'DD/MM/YYYY')) AS FECHAFINAL,
AddMonths(DATE(Date#(FECHAINICIO,'DD/MM/YYYY')),-1) AS FECHAINICIO_ANTERIOR,
PERIODO_TARIFA,
VALOR_CONTRATO,
COSTO_MAYOR,
COSTO_MENOR,
PVP,
PORC_VAR,
INCLUYE,
MANUAL_TARIFARIO,
VALOR_FINAL_SERVICIO,
TIPO_DE_CONTRATO,
NIT_IPS ,
RAZON_SOCIAL AS RAZON_SOCIAL_CONTRATOS,
NOMBRE_SEDE
FROM [lib://qvdsalud (integrasavia_svcqlik)/Contratos.Qvd]
(qvd);

Left Join (La_33_integracions)

IntervalMatch(Fecha_Prestacion,LLAVE1)

LOAD FECHAINICIO,

FECHAFINAL,

LLAVE1

Resident CONTRATOS;

Left Join (La_33_integracions)

LOAD *

Resident CONTRATOS;

DROP Table CONTRATOS;

CONTRATOS_antiguos:
LOAD
NIT_IPS&'-'&COD_SERVICIO&'-'&IF(MODELO_CONTRATA='EVENTO','E','OTROS') AS LLAVE2,
COD_SERVICIO as COD_SERVICIO_CONTRATO_ANTIGUO,
CONTRATO_LEGAL as CONTRATO_LEGAL_ANTIGUO,
IF(trim(MODELO_CONTRATA)='EVENTO','E','OTROS') AS TIPO_CONTRATO_ANTIGUO,
DATE(Date#(FECHAINICIO,'DD/MM/YYYY')) AS FECHAINICIO_CONTRATO_ANTIGUO,
DATE(Date#(FECHAFINAL,'DD/MM/YYYY')) AS FECHAFINAL_CONTRATO_ANTIGUO,
MANUAL_TARIFARIO AS MANUAL_TARIFARIO_CONTRATO_ANTIGUO,
VALOR_FINAL_SERVICIO AS VALOR_FINAL_SERVICIO_CONTRATO_ANTIGUO,

NIT_IPS AS NIT_IPS_CONTRATO_ANTIGUO

FROM [lib://qvdsalud (integrasavia_svcqlik)/Contratos.Qvd]
(qvd);

 

Left Join (La_33_integracions)

NIT_IPS_CONTRATO_ANTIGUO,COD_SERVICIO_CONTRATO_ANTIGUO,TIPO_CONTRATO_ANTIGUO)
IntervalMatch(FECHAINICIO_ANTERIOR,LLAVE2)

LOAD FECHAINICIO_CONTRATO_ANTIGUO,

FECHAFINAL_CONTRATO_ANTIGUO,

LLAVE2
Resident CONTRATOS_antiguos;

 

Left Join (La_33_integracions)

LOAD *

Resident CONTRATOS_antiguos;

DROP Table CONTRATOS_antiguos;

1 Solution

Accepted Solutions
Dalton_Ruer
Employee
Employee

I don't have time to help debug your code below but I wanted to share that I did a webinar on IntervalMatch a while ago. Hopefully something in my content will help point you in the right direction. 

 

https://qlik.webex.com/ec3300/eventcenter/recording/recordAction.do?theAction=poprecord&siteurl=qlik...

View solution in original post

1 Reply
Dalton_Ruer
Employee
Employee

I don't have time to help debug your code below but I wanted to share that I did a webinar on IntervalMatch a while ago. Hopefully something in my content will help point you in the right direction. 

 

https://qlik.webex.com/ec3300/eventcenter/recording/recordAction.do?theAction=poprecord&siteurl=qlik...

View solution in original post