Solved

count, split and extract values from a string

Posted on 2011-03-06
12
343 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 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

19 Experts available now in Live!

Get 1:1 Help Now