topic Re: Data Transformation in QlikView

Data Transformation

Anonymous — Sat, 13 Dec 2014 20:26:06 GMT

I have the following data which need some transformation prior to loading into QlikView:

Substitue	Blank	Drug Code
NORMAL SALINE 1LT		35720
GLIBENCLAMIDE 5MG TABLETS		21345
ENALAPRIL 10MG TABLET		7271
ATORVASTATIN 20 MG TABLET		7020
TENOF300MG+LAMUV300MG+EFAV600MG		50468
COTRIMOXAZOLE 480MG TABLET		20828
AMOXYCILLIN 250MG CAPSULE		20381
CIPROFLOXACIN 500MG		20366
HYOSCINE 10MG TABLET		11241
BESEMAX TABLET		2517

The [Drug Code] should be a six digit number. How can i add preceding zeros to the above to make the [Drug Code] a six number digit at load time?

Regards.

Chris

Re: Data Transformation

datanibbler — Sat, 13 Dec 2014 20:41:33 GMT

Hi Christopher,

well, adding zeros to the left of a number is easy: Sth. like

>> LEFT(('000000' & [Drug code]), 6) <<

HTH

Best regards,

DataNibbler

Re: Data Transformation

MarcoWedel — Sat, 13 Dec 2014 20:50:47 GMT

You probably meant

Right(('000000' & [Drug code]), 6)

LEFT(('000000' & [Drug code]), 6)

will always return '000000'

regards

Marco

Re: Data Transformation

MarcoWedel — Sat, 13 Dec 2014 20:55:25 GMT

Hi,

one solution could be also:

LOAD Substitue,
    Num([Drug Code],'000000') as [Drug Code]
FROM [http://community.qlik.com/thread/145690] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Data Transformation

MarcoWedel — Sat, 13 Dec 2014 21:01:46 GMT

another string operation based approach could be:

Repeat('0',6-Len([Drug Code]))&[Drug Code]

hope this helps

regards

Marco

Re: Data Transformation

its_anandrjs — Sat, 13 Dec 2014 21:03:48 GMT

Hi,

It should be Right( [Drug Code] )

ExcelTab:

LOAD *, Num(Right(AddZeros,6),'000000') as [Drug Code Right];

LOAD *, ('000000' & [Drug Code]) as AddZeros;

LOAD * INLINE [

Substitue, Blank, Drug Code

NORMAL SALINE 1LT, , 35720

GLIBENCLAMIDE 5MG TABLETS, , 21345

ENALAPRIL 10MG TABLET, , 7271

ATORVASTATIN 20 MG TABLET, , 7020

TENOF300MG+LAMUV300MG+EFAV600MG, , 50468

COTRIMOXAZOLE 480MG TABLET, , 20828

AMOXYCILLIN 250MG CAPSULE, , 20381

CIPROFLOXACIN 500MG, , 20366

HYOSCINE 10MG TABLET, , 11241

BESEMAX TABLET, , 2517

];

Regards

Anand

Re: Data Transformation

Anonymous — Sat, 13 Dec 2014 21:04:47 GMT

Marco many thanks for all your input, most appreciated. I will try and revert soonest.

Regards.

Chris

Re: Data Transformation

Anonymous — Sat, 13 Dec 2014 21:06:30 GMT

Many thanks Anand. The list has a thousand plus records so an inline load would be laborious.

Re: Data Transformation

Anonymous — Sat, 13 Dec 2014 21:07:09 GMT

Many thanks for your input

Re: Data Transformation

its_anandrjs — Sat, 13 Dec 2014 21:11:55 GMT

Thanks, No problem you can load from the source file and add this line only in the load script

Load

Num(Right(('000000' & [Drug Code]),6),'000000') as [Drug Code New]

.....Rest Fields

From Source;

Ex:-

Source:

LOAD Substitue, Blank, [Drug Code],

Num(Right(('000000' & [Drug Code]),6),'000000') as [Drug Code New];

LOAD * INLINE [

Substitue, Blank, Drug Code

NORMAL SALINE 1LT, , 35720

GLIBENCLAMIDE 5MG TABLETS, , 21345

ENALAPRIL 10MG TABLET, , 7271

ATORVASTATIN 20 MG TABLET, , 7020

TENOF300MG+LAMUV300MG+EFAV600MG, , 50468

COTRIMOXAZOLE 480MG TABLET, , 20828

AMOXYCILLIN 250MG CAPSULE, , 20381

CIPROFLOXACIN 500MG, , 20366

HYOSCINE 10MG TABLET, , 11241

BESEMAX TABLET, , 2517

];

Regards

Anand

Re: Data Transformation

Anonymous — Sat, 13 Dec 2014 21:23:21 GMT

Many thanks Anand

Re: Data Transformation

MarcoWedel — Sat, 13 Dec 2014 21:48:16 GMT

You're welcome

regards

Marco

Re: Data Transformation

its_anandrjs — Sat, 13 Dec 2014 21:55:04 GMT

Thanks It helps you .

Regards,

Anand