Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# count, split and extract values from a string

Posted on 2011-03-06
Medium Priority
351 Views
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
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
• 7
• 5

LVL 101

Expert Comment

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

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

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 101

Expert Comment

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

mlmcc
0

Author Comment

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 101

Accepted Solution

mlmcc earned 1000 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

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 101

Expert Comment

ID: 35052139

mlmcc
0

Author Comment

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.

0

LVL 101

Expert Comment

ID: 35052593
Either way works

mlmcc
0

Author Comment

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

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

## Featured Post

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
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 â€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/weâ€¦
###### Suggested Courses
Course of the Month9 days, 22 hours left to enroll