Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
May be using IntervalMatch (Extended Syntax) ‒ QlikView
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...
Will be extremely bad for performance, but if you still insist
=Sum(If(Catalog.From <= %Date and Catalog.To >= %Date, Purchases.DCF))
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?
Not sure I understand, but are you saying that you want to give IntervalMatch a shot?
Sure!
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;
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;
Superb Renzo!!! I am glad you were able to play around with it to get what you wanted.
Best,
Sunny