Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
IF(ISNULL(ItemName)=0, ItemName, '001' ) AS ItemName
IF(ISNULL(ItemName)=0, ItemName, '001' ) AS ItemName
Thank You!
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
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
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
Thanks, Steve. That did it.
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