Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chitemerere
Specialist
Specialist

Data Transformation

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

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution could be also:

QlikCommunity_Thread_145690_Pic1.JPG

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

View solution in original post

12 Replies
datanibbler
Champion
Champion

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

Hi,

one solution could be also:

QlikCommunity_Thread_145690_Pic1.JPG

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

View solution in original post

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

];

OP45.png

Regards

Anand

chitemerere
Specialist
Specialist
Author

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

Regards.

Chris

chitemerere
Specialist
Specialist
Author

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

chitemerere
Specialist
Specialist
Author

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