Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ingoniclas
Not applicable

min of date of renewed contracts

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?

Tags (2)
1 Solution

Accepted Solutions
swuehl
Not applicable

Re: min of date of renewed contracts

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.

8 Replies
swuehl
Not applicable

Re: min of date of renewed contracts

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.

ingoniclas
Not applicable

Re: min of date of renewed contracts

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 :-(

swuehl
Not applicable

Re: Re: min of date of renewed contracts

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.

MaxBlock.PNG.png

ingoniclas
Not applicable

Re: min of date of renewed contracts

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.

ingoniclas
Not applicable

Re: min of date of renewed contracts

I finally found and eliminated the error with my real data, now it works. Many thanks!

Not applicable

Re: Re: min of date of renewed contracts

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

Not applicable

Re: Re: min of date of renewed contracts

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

ingoniclas
Not applicable

Re: min of date of renewed contracts

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.