Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
tiklabaq
Creator
Creator

Intervalmatch

Hi Friends,

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

OrderDateMaterialSupplierWerks
Ord12313.01.2016Mat321Sup560001

And a table within the prices:

WerksSupplierMaterialPriceStart DateEnd Date
0001Sup56Mat321100 €01.01.201531.12.2015
0001Sup56Mat321150 €01.01.201631.12.2016
0001Sup75Mat32190 €01.01.201731.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?

1 Solution

Accepted Solutions
aarkay29
Specialist
Specialist

TRY THIS

A:

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 ' ');

B:

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

  A;

exit script;

View solution in original post

6 Replies
victorgil88
Contributor II
Contributor II

Hi Ibrahim,

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

How join can be used with intervalmatch()

Regards

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

tiklabaq
Creator
Creator
Author

Hi Miguel,

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

PO:

OrderDateMaterialSupplierWerks
Ord12313.01.2016Mat321Sup560001

Pricetable:

WerksSupplierMaterialPriceStart DateEnd Date
0001Sup56Mat321100 €01.01.201531.12.2015
0001Sup56Mat321150 €01.01.201631.12.2016
0001Sup75Mat32190 €01.01.201731.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:

OrderDateStart DateEnd DatePrice
Ord12313.01.201601.01.201531.12.2015100
Ord12313.01.201601.01.201631.12.2016150

aarkay29
Specialist
Specialist

TRY THIS

A:

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 ' ');

B:

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

  A;

exit script;

tiklabaq
Creator
Creator
Author

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