topic Re: Intervalmatch in QlikView

Intervalmatch

tiklabaq — Mon, 26 Jun 2017 13:43:22 GMT

Hi Friends,

I have a question about Intervalmatch. I have PO table like this:

Order	Date	Material	Supplier	Werks
Ord123	13.01.2016	Mat321	Sup56	0001

And a table within the prices:

Werks	Supplier	Material	Price	Start Date	End Date
0001	Sup56	Mat321	100 €	01.01.2015	31.12.2015
0001	Sup56	Mat321	150 €	01.01.2016	31.12.2016
0001	Sup75	Mat321	90 €	01.01.2017	31.12.2017

So now I want to have the result if I click on the Ord123 with the Date 13.01.2016, that the correct price is 150€.

I think the Key should be: Werks&Supplier&Material

But I don't know how to make the intervalmatch.

Can you please help me?

Re: Intervalmatch

migueldelval — Mon, 26 Jun 2017 13:45:57 GMT

Hi Ibrahim,

Check this please,

ht tps://community.qlik.com/docs/DOC-3557‌

Slowly Changing Dimensions and IntervalMatch

intevalMatch.pdf

Interval matching methods

Regards

Miguel del Valle

Re: Intervalmatch

victorgil88 — Mon, 26 Jun 2017 13:49:21 GMT

Hi Ibrahim,

There is a discussion where you may find some useful information about intervalmatch:

How join can be used with intervalmatch()‌

Regards

Re: Intervalmatch

Peter_Cammaert — Mon, 26 Jun 2017 13:54:37 GMT

You can first create a key from the three fields in both tables, and then use the Extended Syntax IntervalMatch function. You will still match Date between Start Date and End Date, and match the Key value at the same time. Not too difficult.

See here: IntervalMatch (Extended Syntax) ‒ QlikView

Re: Intervalmatch

tiklabaq — Mon, 26 Jun 2017 14:02:01 GMT

Hi Miguel,

thank you. I tried it already, but it does not work:

PO:

Order	Date	Material	Supplier	Werks
Ord123	13.01.2016	Mat321	Sup56	0001

Pricetable:

Werks	Supplier	Material	Price	Start Date	End Date
0001	Sup56	Mat321	100 €	01.01.2015	31.12.2015
0001	Sup56	Mat321	150 €	01.01.2016	31.12.2016
0001	Sup75	Mat321	90 €	01.01.2017	31.12.2017

My Script is like this:

PO:

*, Werks&Supplier&Material as %Key;

Pricetable:

Werks&Supplier&Material as %Key, [Start Date], [End Date], Price;

Interval:
IntervalMatch(Date) Load [Start Date], [End Date] RESIDENT Pricetable;

And now the new Table is unfortunetaly this:

Order	Date	Start Date	End Date	Price
Ord123	13.01.2016	01.01.2015	31.12.2015	100
Ord123	13.01.2016	01.01.2016	31.12.2016	150

Re: Intervalmatch

aarkay29 — Mon, 26 Jun 2017 14:06:18 GMT

TRY THIS

Load

Werks, Supplier, Material, Date#([Start Date],'DD.MM.YYYY') as [Start Date],Date#([End Date],'DD.MM.YYYY') as [End Date],Price,

Werks&Supplier&Material as Key;

Load * Inline [

Werks Supplier Material Price Start Date End Date

0001 Sup56 Mat321 100 € 01.01.2015 31.12.2015

0001 Sup56 Mat321 150 € 01.01.2016 31.12.2016

0001 Sup75 Mat321 90 € 01.01.2017 31.12.2017

](delimiter is ' ');

Load

Order,Date#([Date],'DD.MM.YYYY') as Date,

Werks&Supplier&Material as Key;

Load * Inline [

Order Date Material Supplier Werks

Ord123 13.01.2016 Mat321 Sup56 0001

](delimiter is ' ');

Inner Join(A)

IntervalMatch(Date,Key)

load

[Start Date],

[End Date],

Key

Resident

exit script;

Re: Intervalmatch

tiklabaq — Tue, 27 Jun 2017 06:36:30 GMT

Thank you very much Aar and all other Guys. My mistake was not to make an inner join to Table (A).

It works now!!!!

Thanks thanks thanks