topic ISNULL Script in QlikView

ISNULL Script

Wed, 27 Oct 2010 13:25:08 GMT

Hi There,

I am trying to display the numbers 001 whenever a value in my load is NULL, but I am struggeling to get it right.

Can someone please try and help. This is what I want to do;

Load

Itemcode,ItemName

But when ItemName = NULL, put a 001 next to Item Code.

Thanks.

ISNULL Script

Wed, 27 Oct 2010 13:30:10 GMT

IF(ISNULL(ItemName)=0, ItemName, '001' ) AS ItemName

ISNULL Script

Wed, 27 Oct 2010 13:35:11 GMT

Thank You!

ISNULL Script

Wed, 27 Oct 2010 13:51:51 GMT

Have you tried somehing like the following:

IF(ISNULL(ItemName)=0, ItemName, '001' )

If not I suggest you put the isnull in the SQL statement within the load file

ISNULL Script

Mon, 06 Jun 2011 21:17:43 GMT

I'm extremely new to Qlikview and I'm trying to do a similiar thing in my Load script.

I'm importing .csv files and sometimes the tax amount field is blank. I can't make the program that's generating these files put $0 in the field instead of just leaving those fields blank, so I'm trying to do that in Qlikview, but I'm not having any luck.

Table I'm loading:

TaxAmount TransactionTotal

15.95

1.00 28.95

Desired Result after load:

TransTaxAmount TransactionTotal

0.00 15.95

1.00 28.95

I've tried:

IF(ISNULL(AmountTax)=0, AmountTax,0) as TransTaxAmount

but TransTaxAmount keeps having blanks instead of 0 which messes up calculations I'm trying to do on that field after it's loaded.

Thanks in advance,

Joyce

ISNULL Script

stevedark — Mon, 06 Jun 2011 21:23:26 GMT

Hi Joyce,

It is likely that the AmountTax field is an empty string (ie. blank) rather than a null. The best bet is a belt and braces approach that checks for both:

if(isnull(AmountTax)=0 and AmountTax <> '', AmountTax, 0) as TransTaxAmount,

You could also try doing a trim on the Amount tax field before checking it is blank - but this should not be neccesary.

Hope that helps.

Regards,

Steve

QlikView Consultant

ISNULL Script

Mon, 06 Jun 2011 22:09:39 GMT

Thanks, Steve. That did it.

Re: ISNULL Script

schivet — Thu, 21 Jul 2011 13:47:04 GMT

Hi Steve,

I used your suggested formula but cannot make it work.

To be sure I didn't do any typo I tried with a simple xls file as a source. It works fine.

When I use my bigger set of data, I first load the data and then do a Resident load.

I got a match when the value is existing but not when NOT existing

Could the Resident Laod be the reason why?

Example 1: Fine

script:

LOAD Name

, Flag

, Country

, if(isnull(Country)=0 and Country <> '', Country, Flag) as [New Country]

FROM

[\\seznt02\sezsch\My Documents\QlikView\Test isnull.xls]

(biff, embedded labels, table is Sheet1$);

Example 2: Not Fine

Script:

Join (DW_PPI_PremPeriodInfo_TB)

Load

// join keys

[PPI RSK Id Original],

[PPI RSK EdtNo],

[PPI PPV Currency],

// field to be added to PPI

if(isnull([PPI RSK Id New])=0 and [PPI RSK Id New] <> '', [PPI RSK Id New], [PPI RSK Id Original]) as [PPI RSK Id]

Resident DW_PPI_PremPeriodInfo_TB

;

Exit Script;

Any idea?

Thanks

Regards

Stéphane