• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

count, split and extract values from a string

I have to the following data strings within a column :
 014=1103021328   - 14 Characters
and
0248=1103051135  - 15 Characters

I understand to pull the date from the the second part after the '=' but sometimes I have 14 Characters and sometime data has 15.

I am trying to formulate how to extract the first part before the '=' (transaction number) and then the second part after the '=' which is actually the date but sometimes the string is 14 characters and sometimes it's 15
0
John-S Pretorius
Asked:
John-S Pretorius
  • 7
  • 5
1 Solution
 
mlmccCommented:
Try this idea

To get each part

Local StringVar Array stringPieces;

stringPieces := Split({YourField},"=");
stringPieces[1] - first part
stringPieces[2] second part

mlmcc
0
 
John-S PretoriusAuthor Commented:
How do I then put : for example, (below) in the according columns :
014=1103021328
0248=1103051135

transaction   date
014                        11/03/05 13:28
0248                      11/03/05 11:35

       
0
 
John-S PretoriusAuthor Commented:
I am so close : but keep getting an error that it's not a string and I can see why as it will get the first transaction if length is 16 and calculate the date but I cannot get it to run the date_compile a second time in a loop. It seems it only runs the first if statement and then use the same value '17' for the next transaction which may be a '16' value hence the 'not a string' error.

Date_compile
if ({@Define aray}) = 17 then
({@extract_date2}) else if ({@Define aray}) = 17 then
({@extract_date1})

define array
length ({PARC_txt.'SerialNo#'})

extract date1
stringvar yyear; // Year
stringvar mmonth; // Month
stringvar dday; // Day
stringvar minhour; //Hour
stringvar minmin; // Minute
yyear := {PARC_txt.'SerialNo#'}[6 to 7]; // YY + 2000
mmonth := {PARC_txt.'SerialNo#'}[8 to 9]; // MM
dday := {PARC_txt.'SerialNo#'}[10 to 11]; // DD
minhour := {PARC_txt.'SerialNo#'} [12 to 13]; // HH
minmin := {PARC_txt.'SerialNo#'}[14 to 15]; // MM
datetime(tonumber(yyear)+2000,tonumber(mmonth),tonumber(dday),tonumber(minhour),tonumber(minmin),0) // seconds are zero

extract date2
stringvar yyear; // Year
stringvar mmonth; // Month
stringvar dday; // Day
stringvar minhour; //Hour
stringvar minmin; // Minute
yyear := {PARC_txt.'SerialNo#'}[7 to 8]; // YY + 2000
mmonth := {PARC_txt.'SerialNo#'}[9 to 10]; // MM
dday := {PARC_txt.'SerialNo#'}[11 to 12]; // DD
minhour := {PARC_txt.'SerialNo#'} [13 to 14]; // HH
minmin := {PARC_txt.'SerialNo#'}[15 to 16]; // MM
datetime(tonumber(yyear)+2000,tonumber(mmonth),tonumber(dday),tonumber(minhour),tonumber(minmin),0) // seconds are zero

extract transaction
stringvar transaction2;
transaction2 := {PARC_txt.'SerialNo#'}[2 to 5];
stringvar transaction;
transaction := {PARC_txt.'SerialNo#'}[2 to 4];
if length({PARC_txt.'SerialNo#'}) = 17 then
transaction2 else transaction

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
mlmccCommented:
I assume each row you are showing is a separate record.

mlmcc
0
 
John-S PretoriusAuthor Commented:
I am grouping {PARC_txt. 'Serial#} and then have all the transaction information for that Groupheading, Eventual I am going to suppress the Data and add the 'pulled-out' information next to the Group heading.

I found a way to get the groupheading information out from the string without any error :
extract_date1
 local stringvar input := {PARC_txt.'SerialNo#'};
 datetime(tonumber(input[6 to 7])+2000, //YEAR
            tonumber(input[8 to 9]), //MONTH
            tonumber(input[10 to 11]), //DAY
            tonumber(input[12 to 13]), //HOUR
            tonumber(input[14 to 15]),0)

extract_date2
 local stringvar input := {PARC_txt.'SerialNo#'};
 datetime(tonumber(input[7 to 8])+2000, //YEAR
            tonumber(input[9 to 10]), //MONTH
            tonumber(input[11 to 12]), //DAY
            tonumber(input[13 to 14]), //HOUR
            tonumber(input[15 to 16]),0)

but now it's only doing it for value '17', if the length is '16' my result is empty.
0
 
mlmccCommented:
Try this formula to format the date and time

Picture(Split({PARC_txt.'SerialNo#'}, '=')[2], 'xx/xx/xx xx:xx')

To get the transaction number

Split({PARC_txt.'SerialNo#'}, '=')[1]

mlmcc
0
 
John-S PretoriusAuthor Commented:
That worked perfect, I spend about an hour on my solution, but yours is far superior - I have so much to learn, thank you.

I just need to know what the ' is on the end - 11/03/02 13:28' (string). Is that showing zero seconds? and how do I get rid of this as I need to take this value convert it to a minutes, take another entry field convert that to minutes and then calculate duration - apply duration to a rate which will give me a theoretical $ amount.

Feel up for the challenge ?
0
 
mlmccCommented:
What are you talking about?

mlmcc
0
 
John-S PretoriusAuthor Commented:
I was hoping that the the description would be at least somewhat 'challenging' but obvuously I am thinking aloud. I guess what I was trying to 'hint' across was that I either continue posting on this thread working the rest of my solution or I start a new question.

Thank you for your assistance.



0
 
mlmccCommented:
Either way works

mlmcc
0
 
John-S PretoriusAuthor Commented:
What I am working on next is to change the extracted value (string,date) to time format - change the last transaction record to time format, subtract from each other and convert to minutes.

This will be Duration and then apply a rate structure (1day=$14,00 : 30 mins intervals =$1 with a max of $14/day) to the duration for the Theoretical Revenue.

Please see the attatched .rpt file for further details. GalthousePARC.rpt
0
 
John-S PretoriusAuthor Commented:
I posted this question again with a point value - hope this works.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now