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.

gradiske
New Contributor III

Problem with date range(between)

I've started a similar discussion a week ago but now it's different. Here's my table.

userstartdateenddateaudience
john01/02/201330/04/20139
mike01/02/201320/09/20135
mike01/09/201331/12/20136
mike30/04/201231/12/20127
john01/04/201331/12/201315
mike01/01/201401/06/20141
john30/04/201405/06/20143
john01/06/201412/12/20144
melissa01/01/201401/06/20144


Note that 'John' first date ends in April 30, but the second date starts with April 1st. So, what i need to do is, consider John's first end date to be March 31, since he started a new one on April 1st.

I've tried to use peek() but since this table has no order at all, and not always the previous dates are the right ones to compare with, it's not working.

9 Replies
gradiske
New Contributor III

Re: Problem with date range(between)

Anyone?

jagannalla
Valued Contributor III

Re: Re: Problem with date range(between)

Hi,

Please find the attachment.

T:

LOAD user,Date#(startdate,'DD/MM/YYYY') as startdate;

LOAD * INLINE [

    user, startdate, enddate, audience

    john, 01/02/2013, 30/04/2013, 9

    mike, 01/02/2013, 20/09/2013, 5

    mike, 01/09/2013, 31/12/2013, 6

    mike, 30/04/2012, 31/12/2012, 7

    john, 01/04/2013, 31/12/2013, 15

    mike, 01/01/2014, 01/06/2014, 1

    john, 30/04/2014, 05/06/2014, 3

    john, 01/06/2014, 12/12/2014, 4

    melissa, 01/01/2014, 01/06/2014, 4

];

LOAD user,

startdate,

If(user=Previous(user),Date(Previous(startdate)-1,'DD/MM/YYYY')) as enddate

Resident T

Order By user,startdate desc;

Thanks,

Jagan

gradiske
New Contributor III

Re: Problem with date range(between)

I get the logic behind that but, in this case every end date will be Startdate -1, when the only case that should be -1 is, IF Startdate is between Start and End of some registry in the table for the same user. So, the end where it matches should be -1.

I've tried to use your example with some if statements but no success. I think peek and previous will not work since the reference it's not always at previous records. That's why i'm having a bigger problem here =/

MVP
MVP

Re: Problem with date range(between)

my attempt

1.png

Re: Problem with date range(between)

Hi,

you could try:

QlikCommunity_Thread_119071_Pic1.JPG.jpg

tabInput:

LOAD * FROM [http://community.qlik.com/thread/119071] (html, codepage is 1252, embedded labels, table is @1);

NoConcatenate

tabOutput:

LOAD

  user,

  startdate,

  If(user=Peek(user) and enddate>=Peek(startdate), Date(Peek(startdate)-1), enddate) as enddate,

  audience

Resident tabInput

Order By user, startdate desc;

DROP Table tabInput;

hope this helps

regards

Marco

jagannalla
Valued Contributor III

Re: Re: Problem with date range(between)

Hi Marco,

I'm surprise to this code!!

tabInput:

LOAD * FROM [http://community.qlik.com/thread/119071] (html, codepage is 1252, embedded labels, table is @1);

Can you please tell me how to develop this script in my local qlikview desktop file.

Thanks,

Jagan

MVP
MVP

Re: Problem with date range(between)

in script editor, there is a button to load from web file (just follow wizard, copy url, etc...)

jagannalla
Valued Contributor III

Re: Re: Problem with date range(between)

Hi Massimo,

Ya I followed, but forgot to add "http:// " in front of the URL. Now it works after adding http://

Gradiske - Did you got your solution ?

Thanks,

Jagan

gradiske
New Contributor III

Re: Re: Problem with date range(between)

I think it's working, just like Massimo's example but, i don't know why its duplicating every registry when i use an IF condition...

Even if there's only one row, like melissa

It's returning something like that

userstartdateenddate
melissa01/01/201431/12/2013
melissa01/01/201401/06/2014

I've tried to reload all the tables using different names and etc, just to make sure it's not using fieldnames from other tables but no luck. If i take out the if for enddate, returns just one.
I'm trying to find where it's causing this, since to get this table i use a lot of load before to get this data...