Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
chintan1989
Contributor II
Contributor II

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 

2 Solutions

Accepted Solutions
Vegar
MVP
MVP

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

sunilpaul
Contributor II
Contributor II

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

 

View solution in original post

9 Replies
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)

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.
tresesco
MVP
MVP

Could you explain the logic?
chintan1989
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
MVP
MVP

Use Subfield() , like:

 

Load

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

From <>;

Vegar
MVP
MVP

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

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!

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
Contributor II
Contributor II
Author

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

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

 

View solution in original post