Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jleefjcapital
Contributor

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
Employee
Employee

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

Would you mind sharing your XML file?

jleefjcapital
Contributor

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

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

Employee
Employee

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

I´m not understanding now...

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

jleefjcapital
Contributor

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

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

Employee
Employee

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

Would you mind sharing your qvw?

jleefjcapital
Contributor

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

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
Contributor

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

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

krishna_2644
Valued Contributor III

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

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]) ;

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

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