Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

ISNULL Script

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

7 Replies
Not applicable
Author

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

Not applicable
Author

Thank You!

Not applicable
Author

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

Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

Thanks, Steve.  That did it.

schivet
Contributor III
Contributor III

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

Image 2011-07-21 010.jpg

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

Image 2011-07-21 011.jpg

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