Handling two sets of dates

[Anonymous]

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 without			Supplier	Fromdate (DateKey)	Todate			New Customer	Lost Customer
table "produktnavn"			ABC inc	01.01.2010	01.07.2010			1	0
			DFG inc	01.07.2010	01.10.2014			0	1
			ABC inc	01.10.2014	01.06.2073			1	0
i want this output		products;	Supplier	Fromdate (DateKey)	Todate	TARFRADATO	TARTILDATO
		11	ABC inc	01.01.2010	01.07.2010	01.01.2010	01.07.2010	1	0
		-	DFG inc	01.07.2010	01.10.2014	01.07.2010	01.10.2014	0	1
		11	ABC inc	01.10.2014	01.01.2015	01.10.2014	01.01.2015	1	0
		17	ABC inc	01.01.2015	01.06.2073	01.01.2015	01.06.2073	0	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.

[adamdavi3s]

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]

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

[Anonymous]

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.

[#6]

Marius,

      What will you want to get?

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

[Anonymous]

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]

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 · Accepted solution]

Intervalmatch extended was the sort of the solution.

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/IntervalMatch_(Extended_Syntax)....