Please find the attachment.
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
If(user=Previous(user),Date(Previous(startdate)-1,'DD/MM/YYYY')) as enddate
Order By user,startdate desc;
Qv_Comm8.qvw 152.8 K
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 =/
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
QlikCommunity_Thread_119071.qvw 148.2 K
I'm surprise to this code!!
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.
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...