Problem with date range(between)

[gradiske]

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

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

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.

[gradiske]

Anyone?

[jagannalla]

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]

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]

my attempt

[MarcoWedel]

Hi,

you could try:

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]

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]

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

[jagannalla]

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]

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

user	startdate	enddate
melissa	01/01/2014	31/12/2013
melissa	01/01/2014	01/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...