Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jleefjcapital
Creator II
Creator II

Using Where Len(Trim())>0 statement to eliminate null values

Hello,

I'm pulling time series data where it's listing out all of the dates regardless of whether there's a corresponding value.   I tried using the Where statement, len(trim())>0 in the data editor, but nothing's changing.  

LOAD

    Date#("date",'YYYY-MM-DD') AS DATE,

     value as AlabamaRGDP,

     value as [REAL GDP],

    'Alabama' as [STATE]

FROM [lib://Real Total Gross Domestic Product by State for Alabama]

(XmlSimple, table is [observations/observation])

WHERE len(trim(value))>0  ;

32 Replies
Clever_Anjos
Employee
Employee

Would you mind sharing your XML file?

jleefjcapital
Creator II
Creator II
Author

The XML file is pulling only the dates where there are values, but Qlik is displaying more dates than necessary within the application.  

Clever_Anjos
Employee
Employee

I´m not understanding now...

Do you have a problem at script level or at graph level?

jleefjcapital
Creator II
Creator II
Author

When I load another dataset with a longer time period, all of the date values are loaded. 

Clever_Anjos
Employee
Employee

Would you mind sharing your qvw?

jleefjcapital
Creator II
Creator II
Author

I think it's fine at the script level, but when I try to graph the data, all of the date values get pulled in.  It's not corresponding to the initial series.

jleefjcapital
Creator II
Creator II
Author

I'm really new to Qlik.   Do I need to download the qvw extension? 

krishna_2644
Specialist III
Specialist III

Did you try using if() Statement instead of Where clause?

LOAD

    Date#("date",'YYYY-MM-DD') AS DATE,

     value as AlabamaRGDP,

     if(len(trim(value))>0,value) as [REAL GDP],

    'Alabama' as [STATE]

FROM [lib://Real Total Gross Domestic Product by State for Alabama]

(XmlSimple, table is [observations/observation]) ;

MarcoWedel

Hi,

not solving your issue, but

WHERE len(trim(value))>0


can be shortened to


WHERE len(trim(value))


because all integers except 0 are interpreted as true.



hope this helps


regards


Marco