Need help with cleaning a column

[chintan1989]

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 

[Lech_Miszkiewicz]

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)

[tresesco]

Could you explain the logic?

[chintan1989]

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

[chintan1989]

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

[tresesco]

Use Subfield() , like:

Load

               SubField([InputCol], '-')  as Output

From <>;

[Vegar · Accepted solution]

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
];

[Lech_Miszkiewicz]

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:

1. are there always only 2 charactes after "-" sign? like -07,-87,-49 etc?
2. is this string always 16 characters
3. when you have example like this, what is your expected output (as in your example sheet you skipped those where second part is less than first part:
   1. 0013586655009-07
      • what do you expect to see here?
   2. 0013586655085-87
      • do yo u expect to see here
        • 0013586655085
        • 0013586655086
        • 0013586655087
      • or only
        • 0013586655085
        • 0013586655087

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!

[chintan1989]

Dear Lech

I got your point. There are many variations in the column. Even the complete scenarios are still not clear as what other variations can be expected in the data.
Thanks for pointing it out.
Time being will use Vegar's provided solution.
Thanks & Regards
Chintan

[sunilpaul · Accepted solution]

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