Solved

count, split and extract values from a string

Posted on 2011-03-06
12
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 
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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 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