Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
scotly-victor
Creator II
Creator II

0's Instead of null in Edit Script

Hi Everyone


I wanted to give 0's Instead of null in Edit Script.

Below fieldname cost has null as well as number ,

[Item cost]:

LOAD

    ItemCode,

   if(isnull(Cost),0,Cost) as Cost

 

FROM [lib://Amazon Data/Item Cost.xlsx]

(ooxml, embedded labels, table is Sheet1);

[ItemCost Temp]:

load ItemCode,avg(Cost) as ItemCost1

RESIDENT [Item cost] group by ItemCode ;

drop table [Item cost];

It didn't work.

15 Replies
Kushal_Chawda

try this

[Item cost]:

LOAD

    ItemCode,

if(len(trim(Cost))=0,0,Cost) as Cost

FROM [lib://Amazon Data/Item Cost.xlsx]

(ooxml, embedded labels, table is Sheet1);

[ItemCost Temp]:

load ItemCode,avg(Cost) as ItemCost1

RESIDENT [Item cost] group by ItemCode ;

drop table [Item cost];

sunny_talwar

Check this link out as well

QlikView Addict: Handling Nulls in QlikView

scotly-victor
Creator II
Creator II
Author

Sorry it didn't work

Kushal_Chawda

Can you share the sample data?

scotly-victor
Creator II
Creator II
Author

if(isnull(Cost),0,Cost)

Works in Front End ,However in Edit Script didn't Work

Colin-Albert

If Cost is a numeric value then you could use Alt()

Alt(Cost, 0) as Cost

Anonymous
Not applicable

It seems that you have some extrange chars on your file. Use RangeAvg.

Regards

scotly-victor
Creator II
Creator II
Author

It had changed rest of numeric value as o

sunny_talwar

Are there joins later on in the script? If there you are doing joins, then you will need to do this nullcheck on the table that is created after the join