formatting a string with hyphens (NDC)

[nlaughton]

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. 

NDC	Name	NDC2
00904198260	ACETAMINOPHEN 325MG TAB	00904-1982-60
00378018105	ALLOPURINOL 300MG TAB	00378-0181-05
00378013701	ALLOPURINOL 100MG TAB	00378-0137-01
55111072910	ALLOPURINOL 100MG TAB	55111-0729-10
67544073660	ALLOPURINOL 100MG TAB	67544-0736-60

[srishsum2017 · Accepted solution]

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]

[CELAMBARASAN]

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