Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
xyz1
Creator III
Creator III

?

.

1 Solution

Accepted Solutions
adamdavi3s
Master
Master

This is a simple way of doing it...

EDIT This is simpler:

source:

LOAD [Account Name]

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE IsNum(left([Account Name],7)) =-1 AND  Match(Mid([Account Name],8),'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') >0;

source:

LOAD *, IsNum(left([Account Name],7)) as numtest, Match(Mid([Account Name],8),'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') as alphatest;

LOAD [Account Name]

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Output:

LOAD *

RESIDENT source

WHERE numtest =-1 AND alphatest >0;

DROP table source;

View solution in original post

4 Replies
xyz1
Creator III
Creator III
Author

.

sunny_talwar

I have not used this before, but I knew something like this existed... won't be able to offer any more help on this topic....

adamdavi3s
Master
Master

This is a simple way of doing it...

EDIT This is simpler:

source:

LOAD [Account Name]

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE IsNum(left([Account Name],7)) =-1 AND  Match(Mid([Account Name],8),'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') >0;

source:

LOAD *, IsNum(left([Account Name],7)) as numtest, Match(Mid([Account Name],8),'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') as alphatest;

LOAD [Account Name]

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Output:

LOAD *

RESIDENT source

WHERE numtest =-1 AND alphatest >0;

DROP table source;