My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Better Than A Complex Switch Function
|
|
 Jan 27 2011, 12:37 PM
Post
#1
|
|
|
UtterAccess Enthusiast Posts: 66 From: Colorado  |
Howdy.
I'm trying to assign list records a segment ID that is based on some math. I'm getting an error that the switch function is too complex. Is there a better way to do this? This should work in theory of receiving a mail code from the user, then attaching a segment field to each record based on their donation performance. ie. ContactKey Segment 23423 NGH1101A 32432 NGH1101E etc... etc... CODE SELECT Donation_Extremes.ContactKey, [MailingCode] & Switch(
[HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_OfflineOnlyDonors].[ContactKey]>0,"A", [HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_OfflineOnlyDonors.ContactKey>0,"B", DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_OfflineOnlyDonors.ContactKey>0,"C", DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_OfflineOnlyDonors.ContactKey>0,"D", DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_OfflineOnlyDonors.ContactKey>0,"E", [HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_MixedChannelDonors].[ContactKey]>0,"H", [HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_MixedChannelDonors.ContactKey>0,"J", DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_MixedChannelDonors.ContactKey>0,"K", DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_MixedChannelDonors.ContactKey>0,"L", DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_MixedChannelDonors.ContactKey>0,"M", [HPC]>99 And DateDiff('m',[MRC],Date())<13 And [Contacts_OnlineOnlyDonors].[ContactKey]>0,"V", [HPC]>99 And DateDiff('m',[MRC],Date())>12 and Contacts_OnlineOnlyDonors.ContactKey>0,"W", DateDiff('m',[MRC],Date())<19 And [HPC]>49 And [HPC]<100 and Contacts_OnlineOnlyDonors.ContactKey>0,"X", DateDiff('m',[MRC],Date())<13 And [HPC]>24 And [HPC]<50 and Contacts_OnlineOnlyDonors.ContactKey>0,"Y", DateDiff('m',[MRC],Date())<10 And [HPC]>9 And [HPC]<25 and Contacts_OnlineOnlyDonors.ContactKey>0,"Z" ) AS Segment, Donation_Extremes.HPC, Donation_Extremes.MRC, Contacts_OnlineOnlyDonors.ContactKey, Contacts_MixedChannelDonors.ContactKey, Contacts_OfflineOnlyDonors.ContactKey FROM (((Donation_Inception INNER JOIN Donation_Extremes ON Donation_Inception.ContactKey = Donation_Extremes.ContactKey) LEFT JOIN Contacts_OfflineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OfflineOnlyDonors.ContactKey) LEFT JOIN Contacts_MixedChannelDonors ON Donation_Extremes.ContactKey = Contacts_MixedChannelDonors.ContactKey) LEFT JOIN Contacts_OnlineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OnlineOnlyDonors.ContactKey; |
 |
 
|
|
Jan 27 2011, 12:45 PM
Post
#2
|
|
|
Rent-an-Admin Posts: 8,756 From: Banana Republic |
1) Sometimes it's the case that "Too Complex" error is actually a consequence of bad syntax - have you tried stripping away one term by one until it works and re-add it back?
2) I have to admit the criteria is not that entirely clear to me and I really think we need to see the source data (at least a bogus representation) and the intended output) - we may be able to suggest a SQL statement in place of Switch(). |
|
|
|
|
Jan 27 2011, 01:09 PM
Post
#3
|
|
|
UtterAccess VIP Posts: 2,441 From: Columbia, Maryland |
I would use a public function to do something this complex.
CODE Public Function fSegment(iHPC, dteMRC, iContactKey) Dim iMonths as Long; iMonths = -99 IF isDate(dteMRC) then iMonths = DateDiff("m",dteMRC,Date()) End if IF iContactKey <= 0 or Imonths = -99 Then 'Return null or empty string fSegment = Null else If iHPC > 99 and imonth < 13 Then fSegment = "A" ElseIf iHPC >99 and iMonth > 12 fSegment = "B" ElseIf iHPC >49 and iHPC <100 and iMonth < 13 fSegment = "C" ElseIf iHPC >24 and iHPC < 50 and iMonth > 12 fSegment = "D" ElseIf iHPC >9 and iHPC < 25 and iMonth < 10 fSegment = "E" ... End IF End IF End function |
|
|
|
|
Jan 27 2011, 01:16 PM
Post
#4
|
|
|
UtterAccess Enthusiast Posts: 66 From: Colorado |
The error is just coming from the length of the switch. If removing segment options, it works again. If I run each set of segments by themselves, it also works fine.
The source data is a queries that gives the donors donation behavior. HPC = highest contribution MRC = most recent contribution and then it also queries their donation activity, such as whether or not they give only online, only through mail, or through both. This query assigns them all codes based on their giving level and their giving activity, then the solicitation package is changed based on the different giving patterns. The source data is in four tables: DonationExtremes ContactKey HPC MRC OnlineOnlyDonors ContactKey OfflineOnlyDonors ContactKey MixedMethodDonors ContactKey The sample output is then: ContactKey Segment 23423 NGH123A 34454 NGH123D 65464 NGH123Z The segment is made up of the mail code supplied by the user ("NGH123" in our example) and the segment assigned by the query. |
|
|
|
|
Jan 27 2011, 01:21 PM
Post
#5
|
|
|
UtterAccess Enthusiast Posts: 66 From: Colorado |
John,
Thanks for that. I think that is the right path to be on. My VBA usage is pretty weak. How do I call that function in my SQL query? is it just something like SELECT Donation_Extremes.ContactKey, [MailingCode] & fSegment(HPC, MRC, ContactKey) as MailingSegment ? Warm Regards, This post has been edited by redphoneconsulting: Jan 27 2011, 01:23 PM |
|
|
|
|
Jan 27 2011, 02:43 PM
Post
#6
|
|
|
UtterAccess Enthusiast Posts: 66 From: Colorado |
Hmm...
Well I've hit a snag, and I can't find the error message. My query looks like this: CODE SELECT Donation_Extremes.ContactKey AS ContactKey, [MailingCode] & fCustomSegment( Donation_Extremes.[HPC], Donation_Extremes.[MRC], Donation_Extremes.[ContactKey], Contacts_OnlineOnlyDonors.ContactKey, Contacts_OfflineOnlyDonors.ContactKey, Contacts_MixedChannelDonors.ContactKey ) AS Segment, Donation_Extremes.HPC, Donation_Extremes.MRC, Contacts_OnlineOnlyDonors.ContactKey As OnlineKey, Contacts_MixedChannelDonors.ContactKey As MixedKey, Contacts_OfflineOnlyDonors.ContactKey AS OfflineKey FROM (((Donation_Inception INNER JOIN Donation_Extremes ON Donation_Inception.ContactKey = Donation_Extremes.ContactKey) LEFT JOIN Contacts_OfflineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OfflineOnlyDonors.ContactKey) LEFT JOIN Contacts_MixedChannelDonors ON Donation_Extremes.ContactKey = Contacts_MixedChannelDonors.ContactKey) LEFT JOIN Contacts_OnlineOnlyDonors ON Donation_Extremes.ContactKey = Contacts_OnlineOnlyDonors.ContactKey; And my function looks like this: CODE Public Function fCustomSegment(iHPC, dteMRC, iExtremeKey, iOnlineKey, iOfflineKey, iMixedKey) Dim iMonths As Long iMonths = -99 If IsDate(dteMRC) Then iMonths = DateDiff("m", dteMRC, Date) End If If iExtremeKey <= 0 Or iMonths = -99 Then 'Return null or empty string fSegment = "pre-else statement fail" Else If iHPC > 99 And iMonth < 13 And iOfflineKey > 0 Then fSegment = "A" ElseIf iHPC > 99 And iMonth > 12 And iOfflineKey > 0 Then fSegment = "B" ElseIf iHPC > 49 And iHPC < 100 And iMonth < 13 And iOfflineKey > 0 Then fSegment = "C" ElseIf iHPC > 24 And iHPC < 50 And iMonth > 12 And iOfflineKey > 0 Then fSegment = "D" ElseIf iHPC > 9 And iHPC < 25 And iMonth < 10 And iOfflineKey > 0 Then fSegment = "E" Else fSegment = "-NoSegment" End If End If End Function But my output isn't returning a segment value of any type. It just looks like this: ContactKey Segment HPC MRC OnlineKey MixedKey OfflineKey 1 MailCode 35 12/4/2010 1 4 MailCode 75 11/8/2010 4 7 MailCode 25 12/1/2010 7 |
|
|
|
|
Jan 27 2011, 03:54 PM
Post
#7
|
|
|
UtterAccess Enthusiast Posts: 66 From: Colorado |
When I renamed the function, I forgot to rename the returned values. Dumb...
Thanks for the help, it's now working correctly. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th May 2013 - 10:49 AM |