topic Re: Remove Null values and White Spaces in QlikView in QlikView

Remove Null values and White Spaces in QlikView

Mon, 29 Sep 2014 11:14:41 GMT

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

Re: Remove Null values and White Spaces in QlikView

sushil353 — Mon, 29 Sep 2014 11:19:26 GMT

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

Re: Remove Null values and White Spaces in QlikView

anbu1984 — Mon, 29 Sep 2014 11:20:57 GMT

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

Re: Remove Null values and White Spaces in QlikView

maxgro — Mon, 29 Sep 2014 11:21:47 GMT

load

...,

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

...

from

Re: Remove Null values and White Spaces in QlikView

ashfaq_haseeb — Mon, 29 Sep 2014 11:21:56 GMT

Hi,

Look at null handling.

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

Regards

ASHFAQ

Re: Remove Null values and White Spaces in QlikView

Mon, 29 Sep 2014 11:34:45 GMT

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

Re: Remove Null values and White Spaces in QlikView

aveeeeeee7en — Mon, 29 Sep 2014 11:37:30 GMT

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

Re: Remove Null values and White Spaces in QlikView

Mon, 29 Sep 2014 12:02:21 GMT

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

Re: Remove Null values and White Spaces in QlikView

anbu1984 — Mon, 29 Sep 2014 12:50:01 GMT

Can you attach sample qvw