Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with contract relationsships and a field with each month for which the contract ist valid, respectively. See an example below showing just one particluar relationship where a contract is valid from Jan 2013 till Mar 2013, and was renewed in Jun 2013:
partner A partner B date
x y 01/01/2013
x y 02/01/2013
x y 03/01/2013
x y 06/01/2013
x y 07/01/2013
The challenge: I want to select the min(date) of the latest contract relationsship, i.e. in this example I want min(date) to be Jun 2013, NOT Jan 2013. Any ideals how I could realize this either in script or chart?
If you want to do it in the script (and that's probably advisable, if the results are not depending on selections),
you can do it like this (just as a start, you don't need to drop all your source data, but then you need to rename fields / create appr. keys):
Set DateFormat = 'MM/DD/YYYY';
INPUT:
LOAD * INLINE [
partner A, partner B, date
x, y, 01/01/2013
x, y, 02/01/2013
x, y, 03/01/2013
x, y, 06/01/2013
x, y, 07/01/2013
];
BLOCKS:
LOAD *,
if(peek([partner A]) = [partner A]
and peek([partner B]) = [partner B]
and date-peek(date) <32,
peek(Block),
rangesum(peek(Block),1)) as Block
Resident INPUT order by [partner A], [partner B], date asc;
drop table INPUT;
RESULT:
LOAD [partner A], [partner B], Block,
date(max(date)) as EndDate,
date(min(date)) as StartDate
Resident BLOCKS group by [partner A], [partner B], Block;
drop table BLOCKS;
In a chart with dimensions partner A and partner B, max(StartDate) will give you the start of the latest contract period.
If you want to do it in the script (and that's probably advisable, if the results are not depending on selections),
you can do it like this (just as a start, you don't need to drop all your source data, but then you need to rename fields / create appr. keys):
Set DateFormat = 'MM/DD/YYYY';
INPUT:
LOAD * INLINE [
partner A, partner B, date
x, y, 01/01/2013
x, y, 02/01/2013
x, y, 03/01/2013
x, y, 06/01/2013
x, y, 07/01/2013
];
BLOCKS:
LOAD *,
if(peek([partner A]) = [partner A]
and peek([partner B]) = [partner B]
and date-peek(date) <32,
peek(Block),
rangesum(peek(Block),1)) as Block
Resident INPUT order by [partner A], [partner B], date asc;
drop table INPUT;
RESULT:
LOAD [partner A], [partner B], Block,
date(max(date)) as EndDate,
date(min(date)) as StartDate
Resident BLOCKS group by [partner A], [partner B], Block;
drop table BLOCKS;
In a chart with dimensions partner A and partner B, max(StartDate) will give you the start of the latest contract period.
Many thanks for your reply, however, I am afraid it did not work. The script ran through flawlessly, but the result was the same as without your snippet 😞
This is strange. The result can't be the same, because I am dropping the original tables and creating a different table with different and new columns.
You are right, I could reproduce your result using the example data. The issue must be in the context of my script. I will have to investigate the error. Many thanks.
I finally found and eliminated the error with my real data, now it works. Many thanks!
Hi;
I hope that can help you!!!!
Check this .qvw
Mmmm for example if you have differente dates:
01/01/2013 up to 07/01/2013
what you need is the penultimate (penultimo) row ???
if this is correct I attached a .qvw that can help.
regardsss!!! and is very sample!!! JUST CHECK the Function: Peek
Sorry I forgot:
the peek() :
(name_colum, number of row, name_table)
(sales,0,'inputtable') as peek
the number 0 mean that will take the first row, for example
sales
10
20
30
40
50
then my result is 10
(sales,1,'inputtable') as peek
then my result is 20
but if EVER I want the penultimate (penultimo) row then use negatives
(sales,-2,'inputtable') as peek
then my result is 40
I have an additional challenge here: I deliver product A to retail stores. Each retail store gets a 10% rebate back from me for every product sold. However, they retail stores sometimes confuse my product A with product B from another company and incorrectly claim rebates from me. In order to see which claims are incorrect I want to make use of the fact that my product A is only good for 90 days after delivery. In order to check on that I want to link my table with the records of my deliveries with each retailers' sales report via the product ID and automatically identify sales made >90 days after my last delivery. How can I achieve this? See an example below:
table with my deliveries to the wholesaler:
Product ID Delivery date Price
A 01/01/2012 20 $
A 01/05/2012 10 $
report of the wholesaler to claim his rebates:
Product ID Sales date Claimed rebate
A 02/15/2012 2 $ -> correct
A 04/10/2012 2 $ -> incorrect, no rebate to be disbursed since it was sold > 90 days after my last delivery. It can't be my product A.
Many thanks for any help.