Consider only transactions during a (promotion) time frame

[cicciput]

Hello,

I have a situation like this:

Qualify *;

Unqualify "%*";

Catalog:

LOAD * INLINE [

    %CatalogID, From, To

    1, 10/6/2017, 15/06/2017

    2, 18/6/2017, 25/06/2017

];

Purchases:

LOAD * INLINE [

    %CatalogID, %Date, DCF

    1, 09/6/2017, 1

    1, 10/6/2017, 1

    1, 14/6/2017, 1

    1, 17/6/2017, 1

    2, 17/6/2017, 1

    2, 19/6/2017, 1

    2, 24/6/2017, 1

    2, 27/6/2017, 1

];

How do I get sum of purchases with %Date between Catalog.From and Catalog.To? It should sum to 2 for both %CatalogID.

Actual case is a bit more complex and involves some set analysis as well, as far as I understand, this is not something I can achieve with set analysis on a row level.

I understand also a solution could be to calculate sum at load time, I would prefer at runtime, because it's not only limited to count purchases: it could be turnover, profit, buyers, etc...

Thanks for your support.

[sunny_talwar]

May be using IntervalMatch (Extended Syntax) ‒ QlikView

[cicciput]

Sorry Sunny,

if I read this right:

The IntervalMatch prefix must be placed before a Load or Select statement

this is something to be done during LOAD, while I'm looking for a runtime solution...

I understand also a solution could be to calculate sum at load time, I would prefer at runtime, because it's not only limited to count purchases: it could be turnover, profit, buyers, etc...

[sunny_talwar]

Will be extremely bad for performance, but if you still insist

=Sum(If(Catalog.From <= %Date and Catalog.To >= %Date, Purchases.DCF))

[cicciput]

Hello Sunny,

thank you, I understand the performance bit,

looking into IntervalMatch I have trouble understanding which is the syntax to add a column to Purchases which states if the purchase has been done during promotion time. This way I could use set analysis to consider only promoted purchases.

It seems to me than IntervalMatch will add a column to the table with the start / end columns while I do need the opposite.

Can you help me?

[sunny_talwar]

Not sure I understand, but are you saying that you want to give IntervalMatch a shot?

[cicciput]

Sure!

[sunny_talwar · Accepted solution]

Something along these lines

Catalog:

LOAD * INLINE [

    %CatalogID, From, To

    1, 10/6/2017, 15/06/2017

    2, 18/6/2017, 25/06/2017

];

Purchases:

LOAD * INLINE [

    %CatalogID, %Date, DCF

    1, 09/6/2017, 1

    1, 10/6/2017, 1

    1, 14/6/2017, 1

    1, 17/6/2017, 1

    2, 17/6/2017, 1

    2, 19/6/2017, 1

    2, 24/6/2017, 1

    2, 27/6/2017, 1

];

Left Join (Purchases)

IntervalMatch(%Date, %CatalogID)

LOAD From,

To,

%CatalogID

Resident Catalog;

[cicciput]

Thanks Sunny,

here what I added to get what I was looking for,

I wish I could have avoided the following join and adding 3 fields to later remove 2 of them...

Catalog:

LOAD * INLINE [

    %CatalogID, From, To

    1, 10/6/2017, 15/06/2017

    2, 18/6/2017, 25/06/2017

];

Purchases:

LOAD * INLINE [

    %CatalogID, %Date, %User, DCF

    1, 09/6/2017, 1, 1

    1, 10/6/2017, 1, 1

    1, 14/6/2017, 1, 1

    1, 17/6/2017, 1, 1

    2, 17/6/2017, 1, 1

    2, 19/6/2017, 1, 1

    2, 24/6/2017, 2, 1

    2, 27/6/2017, 1, 1

];

LEFT JOIN (Purchases) IntervalMatch(%Date, %CatalogID)

LOAD From,

     To,

     %CatalogID

RESIDENT Catalog;

INNER JOIN

LOAD %CatalogID,

     %Date,

     If(IsNull(From), 0, -1) AS InPromo

RESIDENT Purchases;

DROP FIELDS From, To FROM Purchases;

[sunny_talwar]

Superb Renzo!!! I am glad you were able to play around with it to get what you wanted.

Best,

Sunny