Optimize monthly loop

Wed, 20 Apr 2016 14:56:37 GMT

Hi all,

I am trying to generate a loop in order to create months between the start and end date of a contract.

Is it possible to create them directly at month level?

This is my script,

SAHEAD_Periodos:

LOAD

CACTNO as MTO_ContratoNum,

makedate(left(CAVADF,4),mid(CAVADF,5,2),right(CAVADF,2)) AS MTO_InicioContrato,

makedate(left(CAVADT,4),mid(CAVADT,5,2),right(CAVADT,2)) AS MTO_FinContrato

FROM $(vPath)SAHEAD.qvd (qvd) WHERE left(CAAGTP,1) <> 'M' AND (CAVADT>=20150101 OR CAVADF>=20150101);

SAHEAD_Periodos_Loop:

LOAD MTO_ContratoNum, MTO_InicioContrato, MTO_FinContrato, ROWNO() AS MTO_NumeroLinea,

makedate(year(Date(MTO_InicioContrato+iterno()-1)),month(Date(MTO_InicioContrato+iterno()-1))) as MTO_MesVigente

resident SAHEAD_Periodos while MTO_InicioContrato+IterNo()-1 <=MTO_FinContrato;

DROP TABLE SAHEAD_Periodos;

SAHEAD_Ordenado:

LOAD *,

If(previous(MTO_ContratoNum)=MTO_ContratoNum AND previous (MTO_MesVigente)=MTO_MesVigente,0,1) AS Flag_Repetidos

;

LOAD distinct MTO_ContratoNum, MTO_InicioContrato, MTO_FinContrato, MTO_MesVigente, 1 as test

resident SAHEAD_Periodos_Loop order by MTO_ContratoNum,MTO_MesVigente;

DROP TABLE SAHEAD_Periodos_Loop;

DROP FIELD test;

SAHEAD:

LOAD *, 1 as TestLineas

RESIDENT SAHEAD_Ordenado WHERE Flag_Repetidos = 1 AND MTO_MesVigente >=42005 AND MTO_MesVigente <= today();

DROP TABLE SAHEAD_Ordenado;

DROP FIELD TestLineas;

Thanks in advance,

Ana

topic Optimize monthly loop in QlikView