Solved

count, split and extract values from a string

Posted on 2011-03-06
12
344 Views
Last Modified: 2012-05-11
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
Comment
Question by:John-S Pretorius
  • 7
  • 5
12 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35049013
Try this idea

To get each part

Local StringVar Array stringPieces;

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

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 35049075
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
 

Author Comment

by:John-S Pretorius
ID: 35051337
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 35051593
I assume each row you are showing is a separate record.

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 35051772
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 35051792
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:John-S Pretorius
ID: 35051974
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 35052139
What are you talking about?

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 35052240
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 35052593
Either way works

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 35053132
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
 

Author Comment

by:John-S Pretorius
ID: 35053256
I posted this question again with a point value - hope this works.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now