Vdim Issue

[andrea90casa]

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?

[sunny_talwar]

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

];

[andrea90casa]

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

[sunny_talwar]

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

];

[andrea90casa]

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

[sunny_talwar]

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....

[andrea90casa]

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=

[sunny_talwar · Accepted solution]

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

];

[andrea90casa]

Thank you so much Sunny