Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Christopher,
well, adding zeros to the left of a number is easy: Sth. like
>> LEFT(('000000' & [Drug code]), 6) <<
HTH
Best regards,
DataNibbler
You probably meant
Right(('000000' & [Drug code]), 6)
as
LEFT(('000000' & [Drug code]), 6)
will always return '000000'
regards
Marco
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
another string operation based approach could be:
Repeat('0',6-Len([Drug Code]))&[Drug Code]
hope this helps
regards
Marco
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
Marco many thanks for all your input, most appreciated. I will try and revert soonest.
Regards.
Chris
Many thanks Anand. The list has a thousand plus records so an inline load would be laborious.
Many thanks for your input
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