Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jamelmessaoud
Creator
Creator

Expression Possibility

Hi guys,

Can you do something in Qlik via a function/expression to determine if a character in a field is either a text or a number.

I have some postcode data like

  • LA2
  • L40

I want to be able to say if the first character is a letter and the second is a number then do something.  If the first character is a letter and the second character is also a letter then do something else..

Is there a function to do this by any chance?

Thanks

J

1 Solution

Accepted Solutions
benazirkhan
Creator II
Creator II

Hi Jamel!

Try this inside Qlikview:

IF(

ISTEXT(LEFT(CODE, 1)) AND ISNUM(RIGHT(LEFT(CODE, 2),1)),

'action 1',

if(ISTEXT(LEFT(CODE, 1)) AND ISTEXT(RIGHT(LEFT(CODE, 2),1)), 'action2'))


Capture5.PNG


Rgds,

Benazir

View solution in original post

11 Replies
dionverbeke
Partner
Partner

we use applymap to do that and precalculate in the script.

It takes seconds to implement with excel

M:

MAPPING LOAD * INLINE [

From, To

LA, 0

L2, 1

];

...

if(applymap('M',fieldname) = 1, 'Do sth', 'Do sth else')

...

jamelmessaoud
Creator
Creator
Author

Thanks Dion.

I am actually loading this data into Qlik from Excel, so is there a function to do this in Excel? If so, i can sort this from the source!!

Thanks

J

teiswamsler
Partner
Partner

Hi Jamel

If( istext( Left( Postcode, 1)) and isnum( Left( Postcode, 2)), 'Then', 'Else' ) as X

istext and isnum returns true/false

/Teis

dionverbeke
Partner
Partner

yes, create a lookup table and use vlookup

https://www.timeatlas.com/vlookup-tutorial/

dionverbeke
Partner
Partner

Is correct, but this means behaviour is the same across all values. In reality this does not always happen.

Thus the suggestion for a mapping table.

benazirkhan
Creator II
Creator II

Hi Jamel!

Try this inside Qlikview:

IF(

ISTEXT(LEFT(CODE, 1)) AND ISNUM(RIGHT(LEFT(CODE, 2),1)),

'action 1',

if(ISTEXT(LEFT(CODE, 1)) AND ISTEXT(RIGHT(LEFT(CODE, 2),1)), 'action2'))


Capture5.PNG


Rgds,

Benazir

jamelmessaoud
Creator
Creator
Author

This worked a treat. Thanks Benazir and thanks everyone else for your assistance

benazirkhan
Creator II
Creator II

Happy to help Jamel

teiswamsler
Partner
Partner

What a steal congrats