Strip last 3 characters from the field in the select case statement

[tdrBates]

//Command.trip_id_external is a 10 digit string.
//For example for the first Case Command.trip_id_external = '2293098000'.
//How can strip off the last 3 digits of Command.trip_id_external in the select {Command.trip_id_external} statement below?

//I need the trip_id_external string '2293098000' to match '2293098'
//I need the trip_id_external string '2293099000' to match '2293099'


select {Command.trip_id_external}


Case '2293098':
If {Command.seq=num} in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23] then
10
Case '2293099':
If {Command.seq=num} in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23] then
10
Case '2293100':
If {Command.seq=num} in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23] then
10
Case '2293114':
If {Command.seq=num} in [1,2,5,6,7,8,9,10,11,12,13,14,15,16,17] then
10
Else
If {command.seq_num} in [3,4] then
9
Else
If {command.seq_num} in [18] then
11
Else
If {command.seq_num} in [19,20,21,22,23] then
12

Default:
10

 

[tdrBates]

In some cases trip_external_id is only 7 characters, so I would need to incorporate something like the code below in the select case statement.

if len({Command.trip_id_external})<8 then {Command.trip_id_external}
else
left({Command.trip_id_external},len({Command.trip_id_external})-3)

How can you incorporate the select case statement into the if statement?

 

[hilfy]

I might handle it like this:

Local StringVar tripId;
if  len({Command.trip_id_external)) < 8 then
  tripId = {Command.trip_id_external}
else
  tripId = left({Command.trip_id_external},len({Command.trip_id_external})-3);
select {Command.trip_id_external}
  Case '2293098':
    If {Command.seq=num} in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23] then 10
  Case '2293099':
    If {Command.seq=num} in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23] then 10
  Case '2293100':
    If {Command.seq=num} in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23] then 10
  Case '2293114':
    If {Command.seq=num} in [1,2,5,6,7,8,9,10,11,12,13,14,15,16,17] then 10
  Else If {command.seq_num} in [3,4] then 9
  Else If {command.seq_num} in [18] then 11
  Else If {command.seq_num} in [19,20,21,22,23] then 12
Default:
10

-Dell

Senior Manager, Data & Analytics
Protiviti

http://www.protiviti.com

 

[dgillz]

I'd simplify hilfy's code a little:

Local StringVar tripId;
if  len({Command.trip_id_external)) < 8 then
  tripId = {Command.trip_id_external}
else
  tripId = left({Command.trip_id_external},len({Command.trip_id_external})-3);
select {Command.trip_id_external}
  Case  in ('2293098','2293099','2293100'):
    If {Command.seq=num} in [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23] then 10
  Case '2293114':
    If {Command.seq=num} in [1,2,5,6,7,8,9,10,11,12,13,14,15,16,17] then 10
  Else If {command.seq_num} in [3,4] then 9
  Else If {command.seq_num} in [18] then 11
  Else If {command.seq_num} in [19,20,21,22,23] then 12
Default:
10

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html