Seperate Field by string

[Newsense2020]

Hello,

I have an issue with the following topic and hope to find a solution from you experienced minds. I hope the question is placed here correctly.

I have a table with the following customer information:

Customer	Contact
A	Mobile +49 1520999999; E-Mail test@web.de
B	company +49 2222222; Mobile +49 3333333; E-Mail a.tester@gmx.de; Private +49 5555555

So I have several information unstructured in the fields. The goal ist to have the following:

Customer	E-Mail	company	Mobile	Private
A	test@web.de		+49 1520999999
B	a.tester@gmx.de	+49 2222222	Handy +49 3333333	+49 5555555

Does anybody have an idea how to solve that?

Thanks in advance.

[Newsense2020]

@ArnadoSandoval : Your solution worked out perfectly in this case. Now I tried to adopt this procedure to my next challenge. I have attached the exemplary data. Could you or others have a look at it?

[ArnadoSandoval]

@Newsense2020 

Interesting new set of data, I have some questions regarding the Explanation field, for the third row, e.g. Customer 3, the Explanation has two segments: A= and B=, they seems to be separated by a white space, here my questions;

1. Is that always the case? (white space separator)
2. Is it possible that the segments to parse (parsing is the proper word to use, instead of separate field by string) also contains white spaces? the example you posted contains the tokens: Post; Telefon/SMS; E-Mail and Datum, they seems to contain information without white spaces; It is important to know if white spaces appear only between segments.
3. How many segments could be present in the Explanation, e.g. your sample contains segments A= and B=, Is it possible having extra segments, like C=, D=, etc (Is there any limit to the number of segments?)
4. How many tokens (Post; Telefon/SMS; E-Mail and Datum) could appear in each segment?

These segments closely resemble Json strings, very interesting!

I will wait for your reply before starting to enhance the script

[ArnadoSandoval]

@Newsense2020 

I went ahead writing the script, Parsing V2, I replaced the tabs with tildes (~), as the pipe is part of the data we are parsing; the attached application is a solution to your question.

Regards,

[Newsense2020]

@ArnadoSandoval  Thanks for your support and sorry for the late response. I was not working in the meantime.

To your questions:

1. Yes, the white space seperator is always the case

2. I do not know if I understand you right. But for me it is important to know if for example information for A or B is given. But if A is given, allways the information for E-Mail, Telefon etc. will be given (it is then a mandatory field)

3. Actually there is a limit of 7. 

4. This is limited to the given three.

So what I would like to be able to do in the end is to  include into the separation only those, who have for "A" a "j" at E-Mail.

Customer	A_Post	A_Telefon_SMS	A_E_Mail	A_date	B_Post	B_Telefon_SMS	B_E_Mail	B_date
1	N	N	N	12.07.2017	-	-	-	-
2	-	-	-	-	N	N	N	12.07.2017
3	N	N	N	12.07.2017	J	J	J	23.03.2018

When I see that right I will not be able to give that information based on your script, correct?

[ArnadoSandoval]

Hi @Newsense2020 

I attached a new version last week, after asking those questions, Did you try it? I am attaching the QVF here as well.

[Newsense2020]

I tried to understand your logic. But, maybe I am thinking to easy, but couldnt the solution for this problem be based on the following (easy) logic:

1. Index Pos for detecting if there is "A=" and outline Pos_A

2. If there is A=, give me the string from Pos_A until e.g. the 8th sign as A_Post

Because the string is always build up the same, I just need to find out if there is an A=, B= somewhere. In that logic I could build up All Post; Telefon/SMS and E-Mail for all 7 possibilities and only give out the "J" or "N".

A=Post:N|Telefon/SMS:N|E-Mail:N|Datum:2017-07-12

I tried to produce this quite easy logic but failed so far. 

I would appreciate once more somebody to have a solution for that.

[ArnadoSandoval]

Hi @Newsense2020 

Indeed, I misunderstood your question, you want to preserve the fields' groups, e.g. (A=, B=); Question: How many of those groups are present in your data, could you get A=, B=, C= or even more D=, E= or you are always expecting A= and B= (both of them or one or the other). If that is the case your suggestion is spot on, the solution I gave you merge those groups into one huge set of field losing their Lineage! I will wait for your reply regarding the number of 'groups'

HTH

[Newsense2020]

@ArnadoSandoval  I realy appreciate your effort. I just solved my problem. First I used Index for the Groups, then I used your if clause but changed the outcome to the several Positions created through the Index. Starting there and giving e.g. the 7th position after that for The J/N of Post.

[ArnadoSandoval]

@Newsense2020 

I am glad you solved the problem, it was basically what I was about to do over here, but, I am very happy you solved it, because the idea behind us helping you guys is precisely that, making you able to apply the knowledge the community is sharing !!! 🙂👍 

[Newsense2020]

@ArnadoSandoval  Yes and that is why this forum is so great!