topic Re: Handling two sets of dates in QlikView

Handling two sets of dates

Anonymous — Mon, 05 Dec 2016 09:06:14 GMT

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.

Re: Handling two sets of dates

adamdavi3s — Mon, 05 Dec 2016 09:08:22 GMT

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

adamdavi3s — Mon, 05 Dec 2016 09:11:36 GMT

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

Re: Handling two sets of dates

Anonymous — Mon, 05 Dec 2016 09:18:25 GMT

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?

Re: Handling two sets of dates

Gysbert_Wassenaar — Mon, 05 Dec 2016 09:18:47 GMT

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

Mon, 05 Dec 2016 09:31:14 GMT

Marius,

What will you want to get?

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

Re: Handling two sets of dates

Anonymous — Mon, 05 Dec 2016 09:40:28 GMT

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

Anonymous — Mon, 05 Dec 2016 09:51:47 GMT

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.

Re: Handling two sets of dates

Anonymous — Wed, 07 Dec 2016 20:55:52 GMT

Intervalmatch extended was the sort of the solution.

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