8 Replies Latest reply: Dec 7, 2016 3:55 PM by Marius August Larsen RSS

    Handling two sets of dates

    Marius August Larsen

      Hi,

      I need help tp handling two sets of dates / facts.

       

      Tableview:

       

      Taptvunnet contains the facts with supply dates ( datekey ) and supplyer.

      Produktnavn contains the products with from and to date the product was active.

      There can be several products within the daterange of the supplier, but only one active product at the time. se example bellow:

       

              

      This is the output withoutSupplierFromdate (DateKey)TodateNew CustomerLost Customer
      table "produktnavn"ABC inc01.01.201001.07.201010
      DFG inc01.07.201001.10.201401
      ABC inc01.10.201401.06.207310
      i want this outputproducts;SupplierFromdate (DateKey)TodateTARFRADATOTARTILDATO
      11ABC inc01.01.201001.07.201001.01.201001.07.201010
      -DFG inc01.07.201001.10.201401.07.201001.10.201401
      11ABC inc01.10.201401.01.201501.10.201401.01.201510
      17ABC inc01.01.201501.06.207301.01.201501.06.20730

      0

      Output:

      Here is the problem;

      TARFRADATO is not connected to the datekey, therefore i get duplicate rows of all products.

       

      i think i need to generate date range from when the product was active (TARFRADATO - TARTILDATO) within the LEVFRADATO - LEVTILDATO.

        • Re: Handling two sets of dates
          Adam Davies

          Yep there are many different ways to do this.

          Set analysis

          Master calendar with link table

          individual calendars

          canonical calendar

           

          In your case I think you need to do something with SET to return the correct set of products

           

          There are quite a lot of threads on this so a quick search might find you what you want

            • Re: Handling two sets of dates
              Adam Davies

              Actually would something like count( if(TARFRADATO <= LEVFRADATO  AND TARTILDATO >= LEVTILDATO, product) work?

                • Re: Handling two sets of dates
                  Marius August Larsen

                  i need it to be defined in the script, not just with a set analysis in expression.

                   

                  Here is the script;

                   

                  TaptVunnet:

                  LOAD LEVFRADATO as %DateKey,

                       LEVFRADATO,

                       LEVTILDATO,

                       RESPONSIBLE,

                       ETTERNAMN,

                       KUNDENR,

                       KUNDENR as %KundeKey,

                       KONTRAKTNR,

                       KUNDENR&'-'&KONTRAKTNR as KundeKontrakt,

                       VALSTATUS,

                       KONTRAKTFRADATO,

                       KONTRAKTTILDATO,

                       MAALEPKTNR,

                       KONTRGRNR as KontraktGruppeNr,

                       FORRIGERESPONSIBLE,

                       NESTERESPONSIBLE,

                       NESTEKONTRAKTNR,

                       NYKONTRAKT,

                       NYTTMÅLEPUNKT,

                       0 as TAPTKONTRAKT,

                       0 as TAPTTILANNENKRAFTLEV,

                       VUNNETFRAANNENKRAFTLEV,

                       0 as TAPTKUNDE,

                       0 as VOLUMTAPTTILANNENKRAFTLEV,

                       VOLUMVUNNETFRAANNENKRAFTLEV,

                       MÅLEPUNKTAVSLUTTET,

                       MLPKTTILDATO

                  FROM

                  [D:\QlikView\QlikView Storage\1_Extract\NO Elwin\TAPTVUNNETLEVERANSE.qvd]

                  (qvd)

                  where VUNNETFRAANNENKRAFTLEV = 1 OR NYKONTRAKT = 1 or NYTTMÅLEPUNKT = 1 or VOLUMVUNNETFRAANNENKRAFTLEV = 1 // OR TAPTTILANNENKRAFTLEV = 1

                  ;

                   

                   

                  concatenate

                  //left join

                   

                  LOAD LEVTILDATO as %DateKey,

                       LEVFRADATO,

                       LEVTILDATO,

                       RESPONSIBLE,

                       ETTERNAMN,

                       KUNDENR,

                       KUNDENR as %KundeKey,

                       KUNDENR&'-'&MAALEPKTNR as KundeMålepunkt,

                       KUNDENR&'-'&KONTRAKTNR as KundeKontrakt,

                       KONTRAKTNR,

                       VALSTATUS,

                       KONTRAKTFRADATO,

                       KONTRAKTTILDATO,

                       MAALEPKTNR,

                       KONTRGRNR as KontraktGruppeNr,

                       FORRIGERESPONSIBLE,

                       NESTERESPONSIBLE,

                       NESTEKONTRAKTNR,

                       0 as NYKONTRAKT,

                       TAPTKONTRAKT,

                       TAPTTILANNENKRAFTLEV,

                       0 as VUNNETFRAANNENKRAFTLEV,

                       TAPTKUNDE,

                       VOLUMTAPTTILANNENKRAFTLEV,

                       0 as VOLUMVUNNETFRAANNENKRAFTLEV,

                       MÅLEPUNKTAVSLUTTET,

                       0 as NYTTMÅLEPUNKT,

                       MLPKTTILDATO

                  FROM

                  [D:\QlikView\QlikView Storage\1_Extract\NO Elwin\TAPTVUNNETLEVERANSE.qvd]

                  (qvd)

                  where TAPTKUNDE = 1 OR TAPTKONTRAKT = 1 or TAPTTILANNENKRAFTLEV =1 or MÅLEPUNKTAVSLUTTET=1;

                   

                   

                  Produktnavn:

                  LOAD KUNDENR&'-'&KONTRAKTNR as KundeKontrakt,

                       TARIFFNR,

                       TARIFFFRADATO,

                       TARIFFTILDATO,

                       OPPSIGELSESKODE,

                       PRODUKTNAVN,

                       RESEPTNAVN,

                       RESEPTGRPNR,

                       RESEPTID

                  FROM

                  [D:\QlikView\QlikView Storage\1_Extract\NO Elwin\PRODUKTNAVN.qvd]

                  (qvd) where  TARIFFTILDATO <> TARIFFFRADATO;

                   

                  any suggestion?

              • Re: Handling two sets of dates
                Gysbert Wassenaar

                You could try this approach: Creating Reference Dates for Intervals

                Or you could use the IntervalMatch function to match the datekey from the fact table with the interval from the dimension tables.

                • Re: Handling two sets of dates
                  x mg

                  Marius,

                        What will you want to get?

                  There are 4 fields about date,  what will you expect for?

                    • Re: Handling two sets of dates
                      Marius August Larsen

                      i want to see the active procucts within the supplier delivery date.

                      for example:

                       

                      My modell shows this:

                      Supplier ABC had a delivery fromdate (datekey) 01.01.2010 and to date 01.07.2010.

                      i want it to show that In this period we had 2 active products. One from 01.01.2010 to 01.04.2010 and 01.04.2010 to 01.07.2010.

                       

                      Wheni connect the productname table, i get duplicate rows.. because it dosent know what date range the product was active within the datekey.

                       

                      • Re: Handling two sets of dates
                        Marius August Larsen

                        Fromdate(dateKey) and Todate is the whole supplier delivary date.

                        within this daterange there can be several products (with TARFRADATO and TARTILDATO), but only 1 active at the time.