topic Need help with cleaning a column in QlikView

Need help with cleaning a column

chintan1989 — Wed, 12 Jun 2019 06:34:29 GMT

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

Re: Need help with cleaning a column

Lech_Miszkiewicz — Wed, 12 Jun 2019 06:39:23 GMT

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)

Re: Need help with cleaning a column

tresB — Wed, 12 Jun 2019 06:42:40 GMT

Could you explain the logic?

Re: Need help with cleaning a column

chintan1989 — Wed, 12 Jun 2019 07:29:56 GMT

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

Re: Need help with cleaning a column

chintan1989 — Wed, 12 Jun 2019 07:30:54 GMT

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

Re: Need help with cleaning a column

tresB — Wed, 12 Jun 2019 07:34:17 GMT

Use Subfield() , like:

Load

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

From <>;

Re: Need help with cleaning a column

Vegar — Wed, 12 Jun 2019 07:39:57 GMT

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

Re: Need help with cleaning a column

Lech_Miszkiewicz — Wed, 12 Jun 2019 07:53:34 GMT

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:

are there always only 2 charactes after "-" sign? like -07,-87,-49 etc?
is this string always 16 characters
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!

Re: Need help with cleaning a column

chintan1989 — Wed, 12 Jun 2019 10:26:39 GMT

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

Re: Need help with cleaning a column

sunilpaul — Wed, 12 Jun 2019 12:48:44 GMT

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