ISNULL Script

[#1]

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.

[#2 · Accepted solution]

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

[#3]

Thank You!

[#4]

Hi

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

[#5]

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

[stevedark]

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

[#7]

Thanks, Steve.  That did it.

[schivet]

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