Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Friends
Kindly help me get the column clean.
In attached excel , Input Column to be transformed as Output column.
Thanks in advance.
Thanks & Regards
Chintan
LOAD
[Input Column],
LEFT([Input Column], 11) & subfield(Right([Input Column],5),'-') as [Output Column]
INLINE [
Input Column
0013586655006-07
0013586655009-07
0013586655010-11
0013586655012-11
0012699405714-15
0013586655048-49
0013586655085-87
0013586655088-87
0013586655086-87
];
Hi,
Use the code below which will transform the rows and populate the data as you desire.
AAA:
load * Inline
[
Input_Column
0013586655006-07
0013586655009-07
0013586655010-11
0013586655012-11
0012699405714-15
0013586655048-49
0013586655085-87
0013586655088-87
0013586655086-87
];
M1:
NoConcatenate
Load LEFT([Input_Column], 13) as Input_Column Resident AAA;
Concatenate
Load LEFT([Input_Column], 11) & SubField([Input_Column], '-' ,2) as Input_Column Resident AAA;
drop table AAA;
Output////
Input_Column
0012699405714 |
0012699405715 |
0013586655006 |
0013586655007 |
0013586655009 |
0013586655010 |
0013586655011 |
0013586655012 |
0013586655048 |
0013586655049 |
0013586655085 |
0013586655086 |
0013586655087 |
0013586655088 |
You are not giving us any rules you need to have applied, so I am just suggesting you the simplest, based on the fact that number of characters is constant, that you have leading zeros "0" and that you always have 16 charactes in you input column!
Right(Left([Input Column],13),11)
Hello Tresesco
Thanks for your reply.
Logic is like below:
Input column is : 0013586655006-07 ,
so we need to spit this into 2 records as ,13586655006 and 13586655007.
Basically its like adding the part before '-' again to after '-'part.
Regards
Chintan
Hello Lech
Thanks for your reply.
Logic is like below:
Input column is : 0013586655006-07 ,
so we need to spit this into 2 records as ,13586655006 and 13586655007.
Basically its like adding the part before '-' again to after '-'part.
Regards
Chintan
Use Subfield() , like:
Load
SubField([InputCol], '-') as Output
From <>;
LOAD
[Input Column],
LEFT([Input Column], 11) & subfield(Right([Input Column],5),'-') as [Output Column]
INLINE [
Input Column
0013586655006-07
0013586655009-07
0013586655010-11
0013586655012-11
0012699405714-15
0013586655048-49
0013586655085-87
0013586655088-87
0013586655086-87
];
Subfield will split it into multiple rows,
Now since you still need to replace x number of characters the logic is bit more complex.
so here are my questions:
Depending on rules you could use subfield or I rather see here possibility of using while loop with IterNo function...but again rules have to be described with details!
Hi,
Use the code below which will transform the rows and populate the data as you desire.
AAA:
load * Inline
[
Input_Column
0013586655006-07
0013586655009-07
0013586655010-11
0013586655012-11
0012699405714-15
0013586655048-49
0013586655085-87
0013586655088-87
0013586655086-87
];
M1:
NoConcatenate
Load LEFT([Input_Column], 13) as Input_Column Resident AAA;
Concatenate
Load LEFT([Input_Column], 11) & SubField([Input_Column], '-' ,2) as Input_Column Resident AAA;
drop table AAA;
Output////
Input_Column
0012699405714 |
0012699405715 |
0013586655006 |
0013586655007 |
0013586655009 |
0013586655010 |
0013586655011 |
0013586655012 |
0013586655048 |
0013586655049 |
0013586655085 |
0013586655086 |
0013586655087 |
0013586655088 |