topic Re: Consider only transactions during a (promotion) time frame in QlikView

Consider only transactions during a (promotion) time frame

cicciput — Thu, 06 Jul 2017 13:05:34 GMT

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.

Re: Consider only transactions during a (promotion) time frame

sunny_talwar — Thu, 06 Jul 2017 13:14:58 GMT

May be using IntervalMatch (Extended Syntax) ‒ QlikView

Re: Consider only transactions during a (promotion) time frame

cicciput — Thu, 06 Jul 2017 13:19:44 GMT

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...

Re: Consider only transactions during a (promotion) time frame

sunny_talwar — Thu, 06 Jul 2017 13:24:08 GMT

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

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

Re: Consider only transactions during a (promotion) time frame

cicciput — Thu, 06 Jul 2017 14:15:10 GMT

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?

Re: Consider only transactions during a (promotion) time frame

sunny_talwar — Thu, 06 Jul 2017 14:18:45 GMT

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

Re: Consider only transactions during a (promotion) time frame

cicciput — Thu, 06 Jul 2017 14:19:16 GMT

Sure!

Re: Consider only transactions during a (promotion) time frame

sunny_talwar — Thu, 06 Jul 2017 14:23:28 GMT

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;

Re: Consider only transactions during a (promotion) time frame

cicciput — Fri, 07 Jul 2017 10:46:51 GMT

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;

Re: Consider only transactions during a (promotion) time frame

sunny_talwar — Fri, 07 Jul 2017 11:02:42 GMT

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

Best,

Sunny