Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
pra_kale
Creator III
Creator III

How to load data string staring with chracter

Hi,

In below attached application i have a policy number column and in that i am having policy numbers starting with Numbers and Characters

But i want to load policies starting with characters only..how i can do this.

Please help..

Thanks in advance.

1 Solution

Accepted Solutions
santiago_respane
Specialist
Specialist

Hi Prasad,

sure no problem.

Below you will find a possible solution using match:

A:

LOAD PolicyNumber

FROM

[policynumbers.xlsx](ooxml, embedded labels, table is Sheet1)

WHERE NOT match(left(PolicyNumber,1),0,1,2,3,4,5,6,7,8,9);

Kind regards,

View solution in original post

6 Replies
santiago_respane
Specialist
Specialist

Hi Prasad,

you can use the alt function to determine if the first character is a number.

A:

LOAD IF(alt(left(PolicyNumber,1)),PolicyNumber) as PolicyNumber

FROM

[policynumbers.xlsx]

(ooxml, embedded labels, table is Sheet1);

Find sample attached.

Let me know if this helps.

Kind regards,

pra_kale
Creator III
Creator III
Author

Hi Santiago,

Thanks for your help..but actually want other way round. Means I want policy numbers starting with charters only should get upload.

Thanks in advance.

santiago_respane
Specialist
Specialist

Hi Prasad,

my bad, i interpreted totally the opposite.

Here is a possible solution attached.

Please let me know if it fits your needs.

Kind regards,

pra_kale
Creator III
Creator III
Author

Thanks no problem at all...As I have a personal edition i can not open your application so can you please paste the solution the way you posted above.

santiago_respane
Specialist
Specialist

Hi Prasad,

sure no problem.

Below you will find a possible solution using match:

A:

LOAD PolicyNumber

FROM

[policynumbers.xlsx](ooxml, embedded labels, table is Sheet1)

WHERE NOT match(left(PolicyNumber,1),0,1,2,3,4,5,6,7,8,9);

Kind regards,

pra_kale
Creator III
Creator III
Author

Hi,

Thank you very much Santiago...very helpful.