7 Replies Latest reply: Dec 8, 2016 10:19 AM by Marcus Sommer RSS

    IntervalMatch (Extended Syntax)

    Marius August Larsen

      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

          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)
              Marius August Larsen

              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

                  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)
                      Marius August Larsen

                      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

                          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)
                              Marius August Larsen

                              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