topic Vdim Issue in QlikView

Vdim Issue

andrea90casa — Thu, 07 Sep 2017 17:20:11 GMT

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

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?

Re: Vdim Issue

sunny_talwar — Thu, 07 Sep 2017 17:28:16 GMT

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

];

Re: Vdim Issue

andrea90casa — Thu, 07 Sep 2017 17:37:09 GMT

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

Re: Vdim Issue

sunny_talwar — Thu, 07 Sep 2017 17:39:15 GMT

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

];

Re: Vdim Issue

andrea90casa — Thu, 07 Sep 2017 17:43:32 GMT

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

Re: Vdim Issue

sunny_talwar — Thu, 07 Sep 2017 17:45:15 GMT

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

Re: Vdim Issue

andrea90casa — Thu, 07 Sep 2017 22:45:28 GMT

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=

Re: Vdim Issue

sunny_talwar — Thu, 07 Sep 2017 23:49:21 GMT

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

];

Re: Vdim Issue

andrea90casa — Fri, 08 Sep 2017 06:16:49 GMT

Thank you so much Sunny