topic Interval Match Start and Stop Date to Calender in QlikView

Interval Match Start and Stop Date to Calender

thanhphongle — Sat, 16 Nov 2024 19:40:54 GMT

Hello Community,

I have a marketing campaign which has a start and a stop date.

I want to join the period (date between start and stop) to my calender.

Example:

Marketing_Table:

Channel	Article_ID	Start	Stop	Price
Newsletter	A	30.01.2019	31.01.2019	600
Newsletter	A	01.02.2019	05.02.2019	500
Flyer	A	03.02.2019	06.02.2019	400

Calender:

Date
30.01.2019
31.01.2019
01.02.2019
02.02.2019
03.02.2019
04.02.2019
05.02.2019
06.02.2019

Goal Result:

Calendar	Channel	Article_ID	Start	Stop	Price
30.01.2019	Newsletter	A	30.01.2019	31.01.2019	600
31.01.2019	Newsletter	A	30.01.2019	31.01.2019	600
01.02.2019	Newsletter	A	01.02.2019	05.02.2019	500
02.02.2019	Newsletter	A	01.02.2019	05.02.2019	500
03.02.2019	Newsletter	A	01.02.2019	05.02.2019	500
03.02.2019	Flyer	A	03.02.2019	06.02.2019	400
04.02.2019	Newsletter	A	01.02.2019	05.02.2019	500
04.02.2019	Flyer	A	03.02.2019	06.02.2019	400
05.02.2019	Newsletter	A	01.02.2019	05.02.2019	500
05.02.2019	Flyer	A	03.02.2019	06.02.2019	400
06.02.2019	Flyer	A	03.02.2019	06.02.2019	400

I heard about the IntervalMatch Function, but I dont really know how to use it.

Maybe anyone could help me out here?

Thx.

Re: Interval Match Start and Stop Date to Calender

Arthur_Fong — Wed, 13 Nov 2019 14:34:55 GMT

Try this:

Calender:
load Date#(Date,'DD.MM.YYYY')AS Date inline [
Date
30.01.2019
31.01.2019
01.02.2019
02.02.2019
03.02.2019
04.02.2019
05.02.2019
06.02.2019
];

Marketing_Table:
load Channel,Article_ID,Price,Date#(Start,'DD.MM.YYYY') as Start,Date#(Stop,'DD.MM.YYYY')as Stop inline [
Channel,Article_ID,Start,Stop,Price
Newsletter,A,30.01.2019,31.01.2019,600
Newsletter,A,01.02.2019,05.02.2019,500
Flyer,A,03.02.2019,06.02.2019,400
];
inner join
IntervalMatch(Date)
load Start,Stop resident Marketing_Table;

exit SCRIPT;

Re: Interval Match Start and Stop Date to Calender

Stoyan_Terziev — Wed, 13 Nov 2019 14:51:58 GMT

Hello,

I saw Arthur already explained how IntervalMatch works.

I suspect you are looking for the function IterNo() instead of Interval match.

This will generate your table automatically regardless of any calendar ranges:

Marketing_Table: LOAD Channel , Article_ID , Num(Date#(Start, 'DD.MM.YYYY')) AS Start , Num(Date#(Stop, 'DD.MM.YYYY')) AS Stop , Price , RowNo() as Marketing_TableID ; LOAD * INLINE [ Channel, Article_ID, Start, Stop, Price Newsletter, A, 30.01.2019, 31.01.2019, 600 Newsletter, A, 01.02.2019, 05.02.2019, 500 Flyer, A, 03.02.2019, 06.02.2019, 400 ]; For i=0 to NoOfRows('Marketing_Table')-1 LET vEachRowCalID = peek('Marketing_TableID', $(i), 'Marketing_Table'); LET vEachRowCalStart = peek('Start', $(i), 'Marketing_Table'); LET vEachRowCalEnd = peek('Stop', $(i), 'Marketing_Table'); Calendar: LOAD '$(vEachRowCalID)' as Marketing_TableID, Date( Start + IterNo() - 1 ,'DD.MM.YYYY') as Calendar While Start + IterNo() - 1 <= Stop ; LOAD Start, Stop RESIDENT Marketing_Table WHERE Marketing_TableID='$(vEachRowCalID)' ; Trace i; NEXT i; LEFT JOIN(Marketing_Table) LOAD Marketing_TableID, Calendar RESIDENT Calendar; DROP TABLE Calendar;

I hope that helps!

Kind regards,

S.T.

Re: Interval Match Start and Stop Date to Calender

thanhphongle — Wed, 13 Nov 2019 16:25:01 GMT

Hello together,

thank you for giving me a quick answer.

The solution with the intervalmatch works fine for me.

Of course the other solutions are welcome.

Thank you

Best regards Le