Interval match with dates and additional key

[Sebastian_Dec]

Hi, I have similar problem what was in this thopic, but it solution dont halp:
Solved: Interval match with additional key - Qlik Community - 59147
Solved: Re: IntervalMatch Multiplying Records - Qlik Community - 798560

I read regarding IntervalMatch() in these threads:
IntervalMatch - Qlik Community - 1464547
Using IntervalMatch() - Qlik Community - 1475510

My problem: the application duplicates my data after using IntervalMatch(), the correct data merging
is ArtId-Dossier-Date, I even created the key:  Dossier&ArtId as Key1

Here is my code:

Events:
LOAD
    Data as Date,
    Dossier,
    ArtId,
    Dossier&ArtId as Key1,
    CzyPromocja,
    CenaPZ,
    DostawcaId,
    IlościDostarczonePZ_szt,
    WartośćNettoPZ,
    NazwaDostawcy
FROM [lib://QlikContainers/Test/TestFolder/TMP_zakupy/PZ.qvd]
(qvd)
;

Intervals:
LOAD
    ArtId as ArtId2,
    Dossier as Dossier2,
    Dossier&ArtId as Key1,
    Data_Price_from as FromDate,
    Data_Price_to as ToDate,
    Cena_100,
    Cena_zakupu_na_faktutrze_CZF,    
    Cena_netto_na_fakturze_po_rabatach,
    Promo_%,
 	Rabat_na_fakturze_%,
    RowNo
FROM [lib://QlikContainers\Test\TestFolder\TMP_zakupy\SCEBFMET_Final.qvd]
(qvd)
;

IntervalMatch:
IntervalMatch (Date, Key1)
Load distinct FromDate, ToDate, Key1 resident Intervals;

This is what the connections look like:

What does the table look like:

Attached is an Excel file with sample data I use.

[Oleg_Troyansky]

Hi Sebastian,

Intervalmatch may cause duplication of data when there is any overlap between the intervals. In your case, you have a number of typo errors in the table of Intervals:

These overlapping intervals are causing the duplication.

Cheers,

[Sebastian_Dec]

Hi, @Oleg_Troyansky , thanks for the advice, I removed duplicates in the dates and I still have a merging error.
Even if I had duplicates, the date 02/06/2023 should not be combined with the period 01/01/2018-31/12/2018

ArtId 1386 with Dossier 1000 have only one period 01/01/2018-31/12/2018. 

Correct data merging should be ArtId-Dossier-Date, (I even created the key:  Dossier&ArtId as Key1)

[PrashantSangle · Accepted solution]

I recommend to avoid interval match. Use while loop

try below script or modify as per your need.

test:
LOAD
ArtId2,
Dossier2,
Key1,
FromDate,
ToDate,
Cena_100,
Cena_zakupu_na_faktutrze_CZF,
Cena_netto_na_fakturze_po_rabatach,
Promo_%,
Rabat_na_fakturze_%,
"RowNo",
Key1 & "RowNo" as Key2
FROM [lib://data/Intervals_no_duplicates.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join
Load Key1 & "RowNo" as Key2, Date(FromDate + IterNo() - 1) as new_date
Resident test
while FromDate + IterNo() - 1 <= ToDate;

Exit Script;

Regards,

Prashant Sangle

[Ahidhar]

try this

Events:
LOAD
"Date",
Dossier,
ArtId,
//Key1,
CzyPromocja,
CenaPZ,
DostawcaId,
IlościDostarczonePZ_szt,
WartośćNettoPZ,
NazwaDostawcy
FROM [lib://DataFiles/Events.xlsx]
(ooxml, embedded labels, table is Sheet1);

Intervals:
LOAD
ArtId2,
Dossier2,
Key1,
FromDate,
ToDate,
Cena_100,
Cena_zakupu_na_faktutrze_CZF,
Cena_netto_na_fakturze_po_rabatach,
Promo_%,
Rabat_na_fakturze_%,
"RowNo"
FROM [lib://DataFiles/Intervals_no_duplicates.xlsx]
(ooxml, embedded labels, table is Sheet1);

inner Join(Intervals)
IntervalMatch("Date")
IntervalMatch:
Load
FromDate,
ToDate
//Key1
resident Intervals;

[Sebastian_Dec]

Hi @PrashantSangle , thank you very much for your help, it was extremely helpful 🙂

I modified the code:

Events:
LOAD
	Date,
	Dossier,
	ArtId,
	Key1 AS Key1,
	CzyPromocja,
	CenaPZ,
	DostawcaId,
	[IlościDostarczonePZ_szt],
	[WartośćNettoPZ],
	[NazwaDostawcy]
 FROM [lib://QlikContainers\Test\TestFolder\IntervalMatch\Events.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

Intervals:
Left Keep
LOAD
ArtId2,
Dossier2,
Key1,
FromDate,
ToDate,
Cena_100,
Cena_zakupu_na_faktutrze_CZF,
Cena_netto_na_fakturze_po_rabatach,
Promo_%,
Rabat_na_fakturze_%,
"RowNo",
Key1 & "RowNo" as Key2
FROM [lib://QlikContainers\Test\TestFolder\IntervalMatch\Intervals_bezdupli.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join
Load Key1 & "RowNo" as Key2, Date(FromDate + IterNo() - 1) as Date
Resident Intervals
while FromDate + IterNo() - 1 <= ToDate;

Exit Script;

I have one more question, now it makes 8455 rows from 241 rows form Intervals.
Do you know how to make sure only those lines remain that match those 42 lines from Events?

[Sebastian_Dec]

Unfortunately, as you can see from the screenshot, it only creates duplicates / falsifies data.
Such records do not exist in the original data.

[PrashantSangle]

@Sebastian_Dec , you can optimize code by avoiding left join. You can implement same logic in same table

like

test:
LOAD
ArtId2,
Dossier2,
Key1,
FromDate,
ToDate,
Cena_100,
Cena_zakupu_na_faktutrze_CZF,
Cena_netto_na_fakturze_po_rabatach,
Promo_%,
Rabat_na_fakturze_%,
"RowNo",
Date(FromDate + IterNo() - 1) as new_date
//Key1 & "RowNo" as Key2
FROM [lib://data/Intervals_no_duplicates.xlsx]
(ooxml, embedded labels, table is Sheet1)
while FromDate + IterNo() - 1 <= ToDate;

2nd. Is there any specific region to use Left keep.

3rd, Do you know how to make sure only those lines remain that match those 42 lines from Events?

take table add Key2, from_date, to_date and new_date

or

take table add key2 and new_date as dimension and if(Count(new_date)>1,1,0) as measure

if you get value 1 in any measure then you have duplicate value.

Regards,

Prashant Sangle