Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Handling two sets of dates

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

8 Replies
adamdavi3s
Master
Master

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

adamdavi3s
Master
Master

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

Anonymous
Not applicable
Author

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

(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

(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

(qvd) where  TARIFFTILDATO <> TARIFFFRADATO;

any suggestion?

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.


talk is cheap, supply exceeds demand
Not applicable
Author

Marius,

      What will you want to get?

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author