Help me in tuning the data?

[durgabhavani]

Hi All,

I have table with two fields like below.

Points

-1

-4

100

200

500

700

900

0

Payment

2000

-

5000

-

200

300

Code

45b

34c

c36

78cd

How to avoid negative values in points field, null values in payment field, alphabets in code field.

Please give clarity on above scenarios. I have little confusion.

Thanks,

Durga

[sunny_talwar]

Is this the output you trying to get?

Points

1

4

100

200

500

700

900

0

Payment

2000

5000

200

300

Code

45

34

36

78

[durgabhavani]

Yes Sunny!

[sunny_talwar · Accepted solution]

Here is how you will handle negative signs:

(fabs is the key here)

Table:

LOAD fabs(Points) as Points;

LOAD * INLINE [

    Points

    -1

    -4

    100

    200

    500

    700

    900

    0

];

For code you can use KeepChar like this:

Table:

LOAD KeepChar(Code, '0123456789') as Code;

LOAD * INLINE [

    Code

45b

34c

c36

78cd

];

[sunny_talwar]

For Payment you can do somthing like this:

Table:

LOAD Payment

FROM xyz

Where Len(Trim(Payment)) = 0 or IsNull(Payment);

Where Len(Trim(Payment)) <> 0 or not IsNull(Payment);

I am guessing '-' symbol means the cell is empty????

[durgabhavani]

above is correct! Some times i am looking empty and some times null. Then how to handle the data?

[sunny_talwar]

For nulls you can use both Len(Trim(fieldName) and IsNull(fieldName), but for empty I would suggest using Len(Trim(fieldName).

HTH

Best,

Sunny

[durgabhavani]

Thanks sunny,

suppose if i have both null values, empty cells and negative values in one field then what to do to tune the data.

ex:

points

1000

200

-

-300

-

-200

300

-

-34

then how to make the field as below.

1000

200

300

200

300

34

Thanks,

Durga

[sunny_talwar]

I would do something like this:

TempTable:

LOAD * INLINE [

    Points

1000

200

-

-300

-

-200

300

-

-34

];

Table:

LOAD fabs(Points) as Points

Resident TempTable

Where Len(Trim(Points)) <> 0 or not IsNull(Points);

Drop Table TempTable;

Just found out an error in my previous post for checking for nulls and empty post (gave you exact opposite of what was needed):

For Payment you can do somthing like this:

Table:

LOAD Payment

FROM xyz

Where Len(Trim(Payment)) = 0 or IsNull(Payment);

Where Len(Trim(Payment)  <> 0 or not IsNull(Payment);

I am going to update this above also for future references.

Best,

Sunny