Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have one scenario in which i need to do below transformation
i have customer data like below
Customer
0000012234
0000043210
00000REPLU
67890
123445
Total length of the customer is 10 always, what i need to do is if the length is less than than it will automatically add zeros before the number,
e.g. in my case 67890 shud be 0000067890
and 123445 shud be 0000123445
Num(customer,'0000')
Load Num(Customer,'0000000000') as Customer Inline
[
Customer
0000012234
0000043210
00000REPLU
67890
123445
];
Try this:
TAB1:
LOAD *,
If(Len(Customer) < 10, Repeat(0, 10 - Len(Customer)) & Customer, Customer) as CUS;
LOAD * INLINE
[
Customer
0000012234
0000043210
00000REPLU
67890
123445
];
OR
Load
If(Len(Customer) < 10, Text(Repeat(0,10-Len(Customer)))&Customer,Customer) as Customer
Inline
[
Customer
0000012234
0000043210
0000REPLU
67890
123445
];
Ohhh... Sunny T already replied the same...
Hi Guys ,
below also working for me
LEFT('0000000000', 10 - LEN(CUSTOMER)) & CUSTOMER
you can use the repeat function
Load repeat('0',10-Len(Customer) & Customer as Customer
from your_data_source
tried but not working
I think there is missing parenthesis, try this:
LOAD Repeat('0',10-Len(Customer)) & Customer as Customer
T1:
LOAD * INLINE [
Customer
0000012234
0000043210
0000REPLU
67890
123445
];
T2:
NoConcatenate Load
Customer,
Repeat('0',10-len(Customer))&Customer as New
Resident T1;