Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
Luminary
Luminary

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')

...

Highlighted
Creator
Creator

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

Highlighted
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

Highlighted
Luminary
Luminary

yes, create a lookup table and use vlookup

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

Highlighted
Luminary
Luminary

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.

Highlighted
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

Highlighted
Creator
Creator

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

Highlighted
Creator II
Creator II

Happy to help Jamel

Highlighted
Partner
Partner

What a steal congrats