Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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
Yes Sunny!
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
];
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????
above is correct! Some times i am looking empty and some times null. Then how to handle the data?
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
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
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