topic formatting a string with hyphens (NDC) in QlikView

formatting a string with hyphens (NDC)

nlaughton — Sat, 16 Nov 2024 19:34:49 GMT

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

Re: formatting a string with hyphens (NDC)

srishsum2017 — Wed, 27 Nov 2019 03:14:46 GMT

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]

Re: formatting a string with hyphens (NDC)

CELAMBARASAN — Wed, 27 Nov 2019 06:47:08 GMT

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