topic Re: IntervalMatch (Extended Syntax) in QlikView

IntervalMatch (Extended Syntax)

Anonymous — Wed, 07 Dec 2016 20:37:42 GMT

I have a problem with the intervalmatch extended syntax,

see attached file.

it dosent get the tartildato correct when i connect both tables with an intervalmatch.

Output in QV:

TaptVunnettable:

Produktbyttetable:

Re: IntervalMatch (Extended Syntax)

marcus_sommer — Thu, 08 Dec 2016 12:32:39 GMT

Most often I use a while-loop instead of an intervalmatch - take here an look on the example which loads the table "Dates": Loops in the Script.

- Marcus

Re: IntervalMatch (Extended Syntax)

Anonymous — Thu, 08 Dec 2016 13:20:58 GMT

thanks for reply. when am trying to load dates, it loads millions of millions of rows... never ending story.

Do you got an suggestion regardig my application?

Re: IntervalMatch (Extended Syntax)

marcus_sommer — Thu, 08 Dec 2016 13:30:58 GMT

How looked now your script-part with the while-loop? How many records has your table and how many iterations between From and To should be on average?

- Marcus

Re: IntervalMatch (Extended Syntax)

Anonymous — Thu, 08 Dec 2016 14:06:45 GMT

i got 78063167 new date rows when i added this to the script:

Dates:

Load

Kundekontrakt, Date(TARIFFFRADATO + IterNo() -1) as Date

Resident Produktbytte

While IterNo() <= TARIFFTILDATO - TARIFFFRADATO + 1 ;

it dosent get the tartildato correct when i connect both tables with an intervalmatch.

seems like the problem is that it matches TARIFFRADATO with both LEVFRADATO and LEVTILDATO.

Levfradato and levtildato is the datekey.

Re: IntervalMatch (Extended Syntax)

marcus_sommer — Thu, 08 Dec 2016 14:58:43 GMT

Your script to the while-part looked ok. but if I understand you right you made afterwards still the intervalmatch? If yes this needs to be removed. Further your field Kundekontrakt must be the correct key between Dates and Produktbytte - if not you need to add further fields as a combined key maybe within an autonumber().

In general this kind of datamodelling (while-loops and intervalmatch) creates big tables - that's normal and expected but it could be too much for an application in your environment. But unfortunately there aren't much alternatives for them.

One point which could in your case reduce the amount of data would be not to create each date between from and to else only one day of a month maybe the first day created per date(floor(monthstart())).

- Marcus

Re: IntervalMatch (Extended Syntax)

Anonymous — Thu, 08 Dec 2016 15:07:38 GMT

Hi, thanks for reply.

I think one of the problems is the datekey.

Maby if we change levtildato to (date-1) from all other responsible then 4024,25 (my company).

can that fix it?

cuz it seems like the model cant handle levtildato, and levfradato as the same date.

Do you know how i can change LEVTILDATO (efj.todate) to show date-1 where responsible != 4024,25 ?

sql;

select efj.fromdate LevFraDato

,efj.todate LevTilDato

,efj.responsible

,res.etternamn

,efj.kundenr

,efj.kontraktnr

,kon.valstatus

,kon.kontrretur KontraktFraDato

,kon.kontroppseiingdato KontraktTilDato

,kon.maalepktnr

,kon.kontrgrnr

,case when lag (kon.MAALEPKTNR,1) over (partition by kon.maalepktnr order by efj.fromdate) is null then 1 else 0 end as NyttMålepunkt

,lag (efj.responsible,1) over (partition by efj.kundenr,efj.kontraktnr order by efj.fromdate) ForrigeResponsible

,lead (efj.responsible,1) over (partition by efj.kundenr,efj.kontraktnr order by efj.fromdate) NesteResponsible

,lead (kon.kontraktnr,1) over (partition by kon.kundenr order by kon.kontrretur) NesteKontraktNr

,case when lag (efj.responsible,1) over (partition by efj.kundenr,efj.kontraktnr order by efj.fromdate) is null then 1 else 0 end as NyKontrakt

,case when kon.kontroppseiingdato = efj.todate then 1 else 0 end as TaptKontrakt

,case when (lag (efj.responsible,1) over (partition by efj.kundenr,efj.kontraktnr order by efj.fromdate) in (4024,25) and efj.responsible not in (4024,25)) or (lead (efj.responsible,1) over (partition by efj.kundenr,efj.kontraktnr order by efj.fromdate) is null and kon.kontroppseiingdato is not null) then 1 else 0 end as TaptTilAnnenKraftLev

,case when nvl(lag (efj.responsible,1) over (partition by efj.kundenr,efj.kontraktnr order by efj.fromdate),0) not in (4024,25) and efj.responsible in (4024,25) then 1 else 0 end as VunnetFraAnnenKraftLev

,case when nvl(lag (efj.responsible,1) over (partition by efj.kundenr,efj.kontraktnr order by efj.fromdate),0) in (4024,25) and efj.responsible in (4024,25) then 1 else 0 end as VunnetInternt

(4024,25) and efj.responsible not in (4024,25) then 1 else 0 end as FlyttetEksternt

from felles.efmaalepktjurrole efj

left outer join felles.efjurperson res on res.jurpersonid = efj.responsible

left outer join kunde.ekkontrakt kon on kon.kontraktnr = efj.kontraktnr and kon.kundenr = efj.kundenr

where efj.fromdate != efj.todate and nvl(kon.kontroppseiingdato,to_date('2073.06.01','yyyy.mm.dd')) != kon.kontrretur

Re: IntervalMatch (Extended Syntax)

marcus_sommer — Thu, 08 Dec 2016 15:19:21 GMT

I must admit that I couldn't see how levtildato is involved by creating an interval respectively link-table between TARIFFTILDATO and TARIFFFRADATO. Beside them I wouldn't touch the sql-statement I would make all adjustments within a Preceding Load.

- Marcus