Hi Stephen, That is pretty much what someone else told me to do. Thank you for your help.Patti
From: Stephen Markson <smark...@pebc.ca> To: "rbase-l@googlegroups.com" <rbase-l@googlegroups.com> Sent: Thursday, February 23, 2017 1:59 PM Subject: RE: [RBASE-L] - problem with null values #yiv3810627067 #yiv3810627067 -- _filtered #yiv3810627067 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv3810627067 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv3810627067 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv3810627067 {font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;}#yiv3810627067 #yiv3810627067 p.yiv3810627067MsoNormal, #yiv3810627067 li.yiv3810627067MsoNormal, #yiv3810627067 div.yiv3810627067MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv3810627067 a:link, #yiv3810627067 span.yiv3810627067MsoHyperlink {color:blue;text-decoration:underline;}#yiv3810627067 a:visited, #yiv3810627067 span.yiv3810627067MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv3810627067 p {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv3810627067 p.yiv3810627067msonormal, #yiv3810627067 li.yiv3810627067msonormal, #yiv3810627067 div.yiv3810627067msonormal {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv3810627067 p.yiv3810627067msochpdefault, #yiv3810627067 li.yiv3810627067msochpdefault, #yiv3810627067 div.yiv3810627067msochpdefault {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv3810627067 span.yiv3810627067msohyperlink {}#yiv3810627067 span.yiv3810627067msohyperlinkfollowed {}#yiv3810627067 span.yiv3810627067emailstyle18 {}#yiv3810627067 p.yiv3810627067msonormal1, #yiv3810627067 li.yiv3810627067msonormal1, #yiv3810627067 div.yiv3810627067msonormal1 {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv3810627067 span.yiv3810627067msohyperlink1 {color:blue;text-decoration:underline;}#yiv3810627067 span.yiv3810627067msohyperlinkfollowed1 {color:purple;text-decoration:underline;}#yiv3810627067 span.yiv3810627067emailstyle181 {color:#1F497D;font-weight:normal;font-style:normal;}#yiv3810627067 p.yiv3810627067msochpdefault1, #yiv3810627067 li.yiv3810627067msochpdefault1, #yiv3810627067 div.yiv3810627067msochpdefault1 {margin-right:0cm;margin-left:0cm;font-size:10.0pt;}#yiv3810627067 span.yiv3810627067EmailStyle28 {color:#1F497D;font-weight:normal;font-style:normal;}#yiv3810627067 .yiv3810627067MsoChpDefault {font-size:10.0pt;} _filtered #yiv3810627067 {margin:72.0pt 72.0pt 72.0pt 72.0pt;}#yiv3810627067 div.yiv3810627067WordSection1 {}#yiv3810627067 Hi Patti, I’m assuming that you want the most recent date of the three dates that are NOT NULL. I’m also assuming you may want something in particular if all three dates are null. First, set the value you want for the case where all 3 dates are null: SET V vAllNull DATE= whatever date you want or NULL Next find theearliest date in your data: SET V v1 DATE,v2 DATE,v3 DATE,vMin DATE SELECT MIN(Date1),MIN(Date2),MIN(Date3) INTO v1,v2,v3 FROM Table1 SET VAR vMin=(LMIN(.v1,.v2,.v3)-1) Now UPDATE your table: UPDATE Table1 SET MaxDateColumn= + (LMAX((IFNULL(DATE1,.vMin,DATE1)),(IFNULL(DATE2,.vMin,DATE2)),(IFNULL(DATE3,.vMin,DATE3)))) + WHERE DATE1 IS NOT NULL OR DATE2 IS NOT NULL OR DATE3 IS NOT NULL UPDATE Table1 SET MaxDateColumn=.vAllNull + WHERE DATE1 IS NULL AND DATE2 IS NULL AND DATE3 IS NULL Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: 'Patti Jakusz' via RBASE-L [mailto:rbase-l@googlegroups.com] Sent: February-16-17 9:07 AM To: rbase-l@googlegroups.com Subject: Re: [RBASE-L] - problem with null values Yes, I realized I wrote the wrong name. I am looking for the most recent date, not the oldest. (I'm writing emails too fast.) I'm trying to apply this to the entire table at once. I don't know any other way of finding the most extreme date of those three in each record. Patti From: Stephen Markson <smark...@pebc.ca> To: "rbase-l@googlegroups.com" <rbase-l@googlegroups.com> Sent: Wednesday, February 15, 2017 11:27 AM Subject: RE: [RBASE-L] - problem with null values Here are a couple of suggestions. First thing is that LMAX(list of dates) will give you the greatest date, not the oldest. Regardless of what you are looking for, you need to determine what the most extreme date in your data is. Set a variable for that value and then substitute it in Buddy’s formula. Except for numbers with ZERO ON, NULL values can’t be used in any calculations or logical test because R:Base doesn’t know what the value is. E.g. comparing ‘text value’ to NULL always results in FALSE. Is ‘fred’ less than, greater than or equal to NULL? Regards, Stephen Markson The Pharmacy Examining Board of Canada 416.979.2431 x251 From: 'Patti Jakusz' via RBASE-L [mailto:rbase-l@googlegroups.com] Sent: February-14-17 6:37 PM To: rbase-l@googlegroups.com Subject: Re: [RBASE-L] - problem with null values Thank you, Jan. From: jan johansen <j...@jjcalibrations.com> To: rbase-l@googlegroups.com Sent: Tuesday, February 14, 2017 5:16 PM Subject: RE: [RBASE-L] - problem with null values Patti, Buddy beat me to it but sometimes when we are using an older database, we actually saved a space for a null so you may need to check to see if you actually have nulls. Jan -----Original Message----- From: "Buddy Walker" <walker.bu...@comcast.net> To: <rbase-l@googlegroups.com> Date: Tue, 14 Feb 2017 17:11:14 -0500 Subject: RE: [RBASE-L] - problem with null values Patti You might want to try something like this depending on how you want to hand null dates. It my example I’m saying if date is null then make it 12/31/2999 or whatever date you want. …. OldestDate = (LMAX((IFNULL(DATE1,12/31/2999,DATE1)),(IFNULL(DATE2,12/31/2999,DATE2)),(IFNULL(DATE3,12/31/2999,DATE3)))) I didn’t test this so make sure to test on a copy of database Buddy From: 'Patti Jakusz' via RBASE-L [mailto:rbase-l@googlegroups.com] Sent: Tuesday, February 14, 2017 3:38 PM To: rbase-l@googlegroups.com; RBASE-L <rbas...@rbase.com>; RBASE-L <rbase-l+nore...@googlegroups.com> Subject: [RBASE-L] - problem with null values Hello, I'm using Rbase 10. I'm trying to update one date column in my table to be the oldest of 3 other date columns. My command is: update Table1 set OldestDate = (LMAX(date1, date2, date3)) But it only works if all three dates have values. I have zero set on. I tried setting on Eqnull. But I only get a blank if one of the dates is null. Is there another setting to turn on? Thanks, Patti -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email torbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email torbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email torbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email torbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email torbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email torbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to rbase-l+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.