Data Transformation

[Anonymous]

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

[datanibbler]

Hi Christopher,

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

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

HTH

Best regards,

DataNibbler

[MarcoWedel]

You probably meant

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

as

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

will always return '000000'

regards

Marco

[MarcoWedel · Accepted solution]

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

[MarcoWedel]

another string operation based approach could be:

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

hope this helps

regards

Marco

[its_anandrjs]

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

[Anonymous]

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

Regards.

Chris

[Anonymous]

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

[Anonymous]

Many thanks for your input

[its_anandrjs]

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

.....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