varvoura
asked on
comparing dates
Hi all,
If I have datelista and datelistb
I want to compare those so that those in datelista that are smaller than the last date in datelistb are in my result array. Would @replace achieve this result
datelista:05/06/2006, 07/08/2006, 10/10/2006, 10/12/2006, 01/02/2007, 01/03/2007
datelistb:05/06/2006, 10/10/2006, 01/02/2007
resultlist should display: 07/08/2006, 10/12/2006 ONLY. 01/03/2007 date should not appear in the result list because its date is higher than the last date in datelistb.
I've attempted using @replace from all the examples given before but I seem to get either the entire datelista or the entire datelistb
Does anyone have time to give a hand with this?
Thank you all in advance.
If I have datelista and datelistb
I want to compare those so that those in datelista that are smaller than the last date in datelistb are in my result array. Would @replace achieve this result
datelista:05/06/2006, 07/08/2006, 10/10/2006, 10/12/2006, 01/02/2007, 01/03/2007
datelistb:05/06/2006, 10/10/2006, 01/02/2007
resultlist should display: 07/08/2006, 10/12/2006 ONLY. 01/03/2007 date should not appear in the result list because its date is higher than the last date in datelistb.
I've attempted using @replace from all the examples given before but I seem to get either the entire datelista or the entire datelistb
Does anyone have time to give a hand with this?
Thank you all in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant as usual.
I applied it with a couple of small changes and it worked magic.
Better still, I look it up and in help and I know exactly how it works.
Many thanks,
Varvoura
I applied it with a couple of small changes and it worked magic.
Better still, I look it up and in help and I know exactly how it works.
Many thanks,
Varvoura
ASKER
Sjef,
I have a small problem,
this works fine on existing documents, however on new documents i get the value, comparison operators have to be the same type. The two fields are date fields, however, the datelistb is a computed field which does a lookup to get its value and if it is a new product then its original value is null. Is there a way around this problem.
got too excited too soon.
Thanks
I have a small problem,
this works fine on existing documents, however on new documents i get the value, comparison operators have to be the same type. The two fields are date fields, however, the datelistb is a computed field which does a lookup to get its value and if it is a new product then its original value is null. Is there a way around this problem.
got too excited too soon.
Thanks
An additional check should do wonders, but I suppose you found that out already. If not:
lastdate:= @Subset(datelistb; -1);
resultlist:= @If(!@IsTime(lastdate); "";
@Transform(datelista; "x"; @If(!@IsTime(x); @Nothing; x<lastdate; x; @Nothing))
);
lastdate:= @Subset(datelistb; -1);
resultlist:= @If(!@IsTime(lastdate); "";
@Transform(datelista; "x"; @If(!@IsTime(x); @Nothing; x<lastdate; x; @Nothing))
);
ASKER
I did checking first to make sure that the field value I was comparing to wasn't null, then I tried to convert both value to @texttotime before @Transform. Your method is much easier and shorter.
I seem to use a lot of lengthy solutions, i wish I can abbreviate like you do
Thank you
I seem to use a lot of lengthy solutions, i wish I can abbreviate like you do
Thank you
It took me 25 IT-years and a lot of headaches to get these twisted brains. ;)
ASKER
25 years, now that's a lot years. does this mean that I have 20 years before my brain can twist this way????????????????
I have lots of respect for your twisted head though!!!
I have lots of respect for your twisted head though!!!
ASKER