Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gradiske
Contributor III
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
Contributor III
Contributor III
Author

Anyone?

jagannalla
Partner - Specialist III
Partner - Specialist III

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
Contributor III
Contributor III
Author

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 =/

maxgro
MVP
MVP

my attempt

1.png

MarcoWedel

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
Partner - Specialist III
Partner - Specialist III

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

maxgro
MVP
MVP

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

jagannalla
Partner - Specialist III
Partner - Specialist III

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
Contributor III
Contributor III
Author

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