topic Re: Help me in tuning the data? in QlikView

Help me in tuning the data?

durgabhavani — Sat, 28 Mar 2015 17:16:00 GMT

Hi All,

I have table with two fields like below.

Points

-1

-4

100

200

500

700

900

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

Re: Help me in tuning the data?

sunny_talwar — Sat, 28 Mar 2015 17:30:06 GMT

Is this the output you trying to get?

Points

100

200

500

700

900

Payment

2000

5000

200

300

Code

Re: Help me in tuning the data?

durgabhavani — Sat, 28 Mar 2015 17:32:57 GMT

Yes Sunny!

Re: Help me in tuning the data?

sunny_talwar — Sat, 28 Mar 2015 17:37:44 GMT

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

];

For code you can use KeepChar like this:

Table:

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

LOAD * INLINE [

Code

45b

34c

c36

78cd

];

Re: Help me in tuning the data?

sunny_talwar — Sat, 28 Mar 2015 17:39:37 GMT

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

Re: Help me in tuning the data?

durgabhavani — Sat, 28 Mar 2015 17:43:26 GMT

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

Re: Help me in tuning the data?

sunny_talwar — Sat, 28 Mar 2015 19:01:39 GMT

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

Re: Help me in tuning the data?

durgabhavani — Sun, 29 Mar 2015 04:56:08 GMT

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

Thanks,

Durga

Re: Help me in tuning the data?

sunny_talwar — Sun, 29 Mar 2015 12:07:02 GMT

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