Remove Null values and White Spaces in QlikView

[#1]

Office	City	Sales	Amount
12	IND	APPS	566
12	US	PWS	-
-	AUS
	BRA	R

I have a Table Sales.xlx in which i have some dimensions need not to be Null and white spaces For Office,Sales,Amount not to have any NULL values and spacial and White spaces ?How to Remove in QlikView and to show the Table contains Error Data to End User

[sushil353]

Hi,

Try using alt function as

load

Alt(Office,'Error Data') as Office,

Alt(Sales,'Error Data') as Sales,

Alt(amount,'Error Data') as amount

HTH

Sushil

[anbu1984]

If a column has only spaces or null, then assign 'Error' for that column?

Try like this in script

If(Len(Trim(Office))=0,'Error',Office) As Office

[maxgro]

load

...,

if(len(trim(Office))=0, 'Error' Office) as Office,

...

from

[ashfaq_haseeb]

Hi,

Look at null handling.

http://community.qlik.com/docs/DOC-3155

Regards

ASHFAQ

[#6]

Is there any Way to define as follows

If(Len(Trim(Office))=0,'Error',Office) As Office throws Error in Any Dimension it should not loaded into QlikView.

What am Expecting is some Dimensions and Measures Should not Contain any White spaces and Null .If any column contain White space and Null Value whole table to be dropped old Table data only Loaded.Dropped table should Be represented in App Suppose Error Log table With failure Table Contents

[aveeeeeee7en]

Hi

Try this Script:

Table1:

LOAD *, if(Len(Trim(Office))=0 OR Office='-','ERROR',Office) AS New_Office,

if(Len(Trim(Sales))=0 OR Sales='-','ERROR',Sales) AS New_Sales,

if(Len(Trim(Amount))=0 OR Amount='-','ERROR',Amount) AS New_Amount  INLINE [

Office, City, Sales, Amount

12, IND,  APPS, 566

12, US,  PWS, -

-,  AUS,    ,

, BRA,  R,

];

Output Comparison:

Regards

Av7eN

[#8]

Aveeeeeee7en Thanx for Your Solution But Not Expecting That Desired One  if any column in the above Table contains a Null or Blank value should Through Error while Loading Data from Source .Bad Data or Incomplete Data With Appropriate Error Message to End User

[anbu1984]

Can you attach sample qvw