Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi at all,
I have a field with a several codes concatenated and separated by semicolon. This field is called Variables and for example he has these values:
1) AAA;VDIM=125~54~45,LLL
2)VDIM=100~45~55;CCC;CCC
3)AAA;BBB;VDIM=100~58~63
....
....
What i would like to do is to create a second field in the script that give to me only the value VDIM=....~....~.... for any records.
I have tried this formula:
SubField(Variables,';',1)
That gives to me the follow results for the three records:
1) AAA
2) VDIM=100~45~55
3)AAA
I have created this because i wanted to use this second formula that extract the string that i need if the result is VDIM=
IF(
Left(SubField(Variables,';',1),5) = 'VDIM=',
SubField(Variables,';',1),
SubField(Variables,';',2)
)
But i would like to create an automatic formula that starts to find where is the position of VDIM= and then estract only the data that I need. So the logic should be this one:
a = 1
IF
Left(SubField(Variables,';',a),5) = 'VDIM='
IS TRUE THEN
SubField(Variables,';',a)
ELSE
a + 1
AND SO ON
Is there a way in order to do it? Or there are other ways in order to reach the same result?
Here you go...
Table:
LOAD Field,
If(Len(Trim(TextBetween(Field & ';', 'VDIM=', ';'))) > 0, 'VDIM=' & TextBetween(Field & ';', 'VDIM=', ';')) as NewField;
LOAD * INLINE [
Field
AAA;VDIM=125~54~45,LLL
VDIM=100~45~55;CCC;CCC
AAA;BBB;VDIM=100~58~63
AAA
];
Try this
Table:
LOAD Field,
'VDIM=' & TextBetween(Field, 'VDIM=', ';') as NewField;
LOAD * INLINE [
Field
AAA;VDIM=125~54~45,LLL
VDIM=100~45~55;CCC;CCC
AAA;BBB;VDIM=100~58~63
];
Hi Sunny thank you very much for your help. I tried but it works only for one records, the other gives me only VDIM= and not the value that i need:
1)VDIM=125~54~45
2)VDIM=100~45~55
3)VDIM=100~58~63
Andrea
My bad, I missed a key thing here (in red below)... try this
Table:
LOAD Field,
'VDIM=' & TextBetween(Field & ';', 'VDIM=', ';') as NewField;
LOAD * INLINE [
Field
AAA;VDIM=125~54~45,LLL
VDIM=100~45~55;CCC;CCC
AAA;BBB;VDIM=100~58~63
];
Thanks Sunny,
Sometimes it happen that field variables doesn't have a VDIM=.... but only other text like AAA, or sometimes the records is empty .
In that case i can use the same formula or i have to add IF statement or somethin else more efficient?
Andrea
As long as this gives you the right output (I am guessing it would give null for the NewField in those cases) for the cases you mentioned, I would just use this and not look for alternatives....
Yes i checked and when there is only one record like VDIM=....~....~ the output is correct, when the record is for example AAA then the output it is not correct because it gives VDIM=
Here you go...
Table:
LOAD Field,
If(Len(Trim(TextBetween(Field & ';', 'VDIM=', ';'))) > 0, 'VDIM=' & TextBetween(Field & ';', 'VDIM=', ';')) as NewField;
LOAD * INLINE [
Field
AAA;VDIM=125~54~45,LLL
VDIM=100~45~55;CCC;CCC
AAA;BBB;VDIM=100~58~63
AAA
];
Thank you so much Sunny