Preventing duplicate records with IntervalMatch in QlikView

[italobonini]

Hi everyone,

I'm working on a QlikView 12 application where I need to match sales transactions with promotional periods.

For each sale, I need to identify the promotion that was active for that article on the transaction date.

The promotion table contains something like:

ArticleCode | StartDate  | EndDate    | PromotionCode
------------|------------|------------|--------------
A001        | 2026-09-01 | 2026-09-10 | PROMO01
A001        | 2026-09-05 | 2026-09-15 | PROMO02
A002        | 2026-09-01 | 2026-09-30 | PROMO03

And the sales table contains:

ArticleCode | SaleDate   | Quantity
------------|------------|---------
A001        | 2026-09-07 | 2
A001        | 2026-09-08 | 1
A002        | 2026-09-15 | 5

I am currently using IntervalMatch to associate the sales date with the promotional interval.

The problem occurs when two promotional intervals for the same article overlap.

For example:

A001 | 2026-09-01 | 2026-09-10 | PROMO01
A001 | 2026-09-05 | 2026-09-15 | PROMO02

A sale on 2026-09-07 matches both promotions, so the sales record is duplicated.

This is expected behavior from IntervalMatch, but in my case I need to apply a business rule so that each sale is associated with only one promotion.

For example, if PROMO02 has priority over PROMO01, I would want:

A001 | 2026-09-07 | PROMO02

instead of:

A001 | 2026-09-07 | PROMO01
A001 | 2026-09-07 | PROMO02

The real script is more complex because there are several additional fields involved, but the basic problem is the same.

What would be the recommended QlikView approach to handle this?

Thank you!

[alejandroquinones]

Hey @italobonini ,

You can solve it this way:

1. Create a bridge table. Evaluate each SaleDate against active promotional intervals defined by StartDate and EndDate for every ArticleCode. When multiple promotional windows overlap, the bridge table captures all valid combinations.

2. Get promotion attributes: Left-join the promotion details back onto the bridge table so each matched date range includes its corresponding PromotionCode and StartDate.

3. Join bridge with Sales table. Use FirstSortedValue to get last promotion: Left-join the bridge data into the Sales table, grouped by ArticleCode and SaleDate. The expression FirstSortedValue(PromotionCode, -StartDate) ranks matches by StartDate descending, isolating the latest promotion code

Find attached the .qvw file. Hope this works for you

Regards!

[marcus_sommer]

I would tend to do the date-resolution within a while-loop and then applying either an aggregating on it or a flagging/filtering the result. This might look like:

t1: load Article, Promotion, date(Start + iterno() -1) as Date
     while Start + iterno() -1 <= End;

t2: load Article, Date, concat(Promotion, ' + ') as PromotionList
     resident t1;

t3: load Article, Date, Promotion,
             if(Article <> previous(Article), 1,
             if(Date <> previous(Date), 1, peek('Nr') + 1)) as Nr
     resident t1 order by Article, Date;

It just shows the main-approach and both t2 + t3 would need to include further sorting-information to define the wanted order, like the min/max promotion-value or the min/max Start/End or whatever logic should be applied. The final result might then be joined/mapped to the facts and/or kept as separate dimension-table.

[rubenmarin]

Hi, if you only want 1 promotion i would work with the 'promotion table' so the periodos don't overlap, solo the promotions by priority and check that each promtion starts when the higher priority promotion has ended.

Like:

If(Peek(EndDate)>=StartDate, Date(Peek(EndDate)+1), StartDate) as StartDate

Maybe you also need to check EndDates in case some promotion don't have any date (other promotions covers all it's dates. And probably a final check to remove those promotions from the final table.

Having the 'promotion table' without overlapping periods will alow you to keep the rest of the script as it was.