Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Announcements
Register for public instructor-led classes and save 50%. Use promo code 2020TRAIN50. START LEARNING
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Can I apply a string function to a field I get via a SQL select statement

newbie question here I'm sure:

I need to clean up a text string that is retrieved as a field from a SQL Select statement so that I can join it to another field loaded from a Table file. I am trying to avoid having to do multiple replace() functions in my select statement and was hoping to rely on the allow() function in QlikView. So, is it possible to update the value of a field that is loaded via SQL select and if so how.

Ideally I'd be able to do something like:

ODBC CONNECT TO SOME_DB;

LET DNIS = string.allow(SOME_DB.DNIS,'0123456789');

SQL SELECT ..., phone_home as DNIS... FROM SOME_DB.leads where deleted <> 1;

so that the field DNIS now only includes numeric characters.

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

I would suggest you make this kind of cleaning a preceeding load:

ODBC CONNECT TO SOME_DB;

Load keepchar(DNIS, '0123456789') as DNIS;

SQL SELECT ..., phone_home as DNIS... FROM SOME_DB.leads where deleted <> 1;

- Marcus

View solution in original post

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

I would suggest you make this kind of cleaning a preceeding load:

ODBC CONNECT TO SOME_DB;

Load keepchar(DNIS, '0123456789') as DNIS;

SQL SELECT ..., phone_home as DNIS... FROM SOME_DB.leads where deleted <> 1;

- Marcus

View solution in original post

Highlighted
Not applicable

Marcus,

thank you. That worked exactly as expected.

Regards,

Paul

Highlighted
Contributor III
Contributor III

@Paul; You should mark Marcus succession as answer.

@Marcus; that was helpful suggestion

Highlighted
Not applicable

Thank you.