Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
nlaughton
New Contributor II

formatting a string with hyphens (NDC)

How can i  convert a number field to a string with a "#####-####-##" format. 

like the the NDC2 column. I would like to do this on the load statement.  Any help would be very much appreciated. as a wrinkle some of the NDC data does not have 11 characters and leading 0's would need to be added to make an 11 character string. I attached a sample qlikview document and the underlying excel data. 

NDCNameNDC2
00904198260ACETAMINOPHEN 325MG TAB00904-1982-60
00378018105ALLOPURINOL 300MG TAB00378-0181-05
00378013701ALLOPURINOL 100MG TAB00378-0137-01
55111072910ALLOPURINOL 100MG TAB55111-0729-10
67544073660ALLOPURINOL 100MG TAB67544-0736-60

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
srishsum2017
Contributor

Re: formatting a string with hyphens (NDC)

Try the below mentioned statement-

LOAD NDC,
Left(Repeat('0',6-Len(Trim(NDC)))&Trim(NDC),5)&'-'&Left(Mid(Repeat('0',6-Len(Trim(NDC)))&Trim(NDC),6),4)&'-'&Right(Repeat('0',6-Len(Trim(NDC)))&Trim(NDC),2) as NDC2,

Name
FROM
[D:\ndc format.xlsx]

 

Sumit Kumar Srivastava

View solution in original post

2 Replies
Highlighted
srishsum2017
Contributor

Re: formatting a string with hyphens (NDC)

Try the below mentioned statement-

LOAD NDC,
Left(Repeat('0',6-Len(Trim(NDC)))&Trim(NDC),5)&'-'&Left(Mid(Repeat('0',6-Len(Trim(NDC)))&Trim(NDC),6),4)&'-'&Right(Repeat('0',6-Len(Trim(NDC)))&Trim(NDC),2) as NDC2,

Name
FROM
[D:\ndc format.xlsx]

 

Sumit Kumar Srivastava

View solution in original post

Highlighted

Re: formatting a string with hyphens (NDC)

If you don't have any issues in fliping the Thousands separator, then try the below option

 

SET ThousandSep='-';
LOAD *, Text(Num(NDC, '00000-0000-00')) as NDC3 INLINE [
NDC, Name, NDC2
00904198260, ACETAMINOPHEN 325MG TAB
00378018105, ALLOPURINOL 300MG TAB
00378013701, ALLOPURINOL 100MG TAB
55111072910, ALLOPURINOL 100MG TAB
67544073660, ALLOPURINOL 100MG TAB
];
SET ThousandSep=',';//Default one