topic Re: Extract a Value from Field after Character in QlikView

Extract a Value from Field after Character

Tue, 23 Sep 2014 14:13:33 GMT

Hello,

I have a field named QTY where it could be like:

What I need is to extract from that field the number that comes after the x (or it could be an X too).

For the examples shown, I should get the following:

How could I do that?

Thank you!!!

---

I tried using SUBFIELD(QTY,'x',2).

I think it works, but I'd need to use the logic with SUBFIELD(QTY,'X',2) too.

demoustier — Tue, 23 Sep 2014 14:19:38 GMT

you can try

right('Field',index('Field',or('x','X'),-1)

PS: not sure for the or('x','X')

mphekin12 — Tue, 23 Sep 2014 14:26:04 GMT

What if you try something like this:

if(isnull(SUBFIELD(QTY,'x',2)),

SUBFIELD(QTY,'X',2),

SUBFIELD(QTY,'x',2)

)

mphekin12 — Tue, 23 Sep 2014 14:34:18 GMT

Hope the attached file helps!

maxgro — Tue, 23 Sep 2014 14:36:58 GMT

right(QTY , len(QTY ) - index(upper(QTY ), 'X') )

Tue, 23 Sep 2014 14:50:53 GMT

EDIT:

I didn't read the 'X' problem.

I've tryed this:

LET vTEST = '1,2k1000/3Ax1';

LET vRESULT = TextBetween (Replace(vTEST,'x', 'X'),'X','');

And it worked.

So:

To solve your problem you could 'create' a new field in that table using:

TextBetween (Replace(YourField,'x', 'X'), 'x', '' ) AS ValueAfterX;

Good Luck!

MarcoWedel — Tue, 23 Sep 2014 15:08:55 GMT

SUBFIELD(Upper(QTY),'X',2)