Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
chintan1989
New Contributor

Need help with cleaning a column

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 

1 Solution

Accepted Solutions
Partner
Partner

Re: Need help with cleaning a column

image.png

 

 

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

 

Please ekskuse my Norglish and Swenglish typos.
9 Replies

Re: Need help with cleaning a column

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)

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
MVP
MVP

Re: Need help with cleaning a column

Could you explain the logic?
chintan1989
New Contributor

Re: Need help with cleaning a column

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
New Contributor

Re: Need help with cleaning a column

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

MVP
MVP

Re: Need help with cleaning a column

Use Subfield() , like:

 

Load

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

From <>;

Partner
Partner

Re: Need help with cleaning a column

image.png

 

 

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

 

Please ekskuse my Norglish and Swenglish typos.

Re: Need help with cleaning a column

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!

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
chintan1989
New Contributor

Re: Need help with cleaning a column

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
New Contributor

Re: Need help with cleaning a column

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