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

[jleefjcapital]

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  ;

[Anonymous]

But you're are not using any date restrictions.  You're filtering out only records where value is null or blank string.  It very well may be that there are non-empty values in all dates.

Besides, if DATE is a key field, the values may be coming from another logical table.

Anyway, it is just a guess.  You have to upload your application so we can help better.

Not related but wondering why you need value two times under different names:

   value as AlabamaRGDP,

   value as [REAL GDP],

[jleefjcapital]

I wanted to be able to pull all of the states as well as one state at a time.

Also, would you be able to help me upload my file?  I'm not sure how to do this.   It seems really basic, but I don't even know where to start.

Thank you.

[MarcoWedel]

hope this helps

regards

Marco

[Anonymous]

To upload file:

In the "Reply ..." window, where you type the replies, find "Use advanced editor" link on the top right.  Click on it.  After that, you'll see "Attach" link on the bottom right.  Click, brows to you file, add reply.  That's it.

[jleefjcapital]

Thank you, but I can't seem to save the files in the first place. ...

[jleefjcapital]

I think the problem is I only have Qlik desktop.  I may need server for this.  Is there another way for me to send you this information?  

[Anonymous]

Nope.  You don't need QV Server.  In fact, to send a file you just need this file, nothing else.

[ToniKautto]

Based on your example script the issue is when you use Qlik Sense, so I moved the thread to "New To Qlik Sense" section instead of "New To QlikView".

In Qlik Sense Desktop the apps are stored as QVF file under My Documents in Windows. Typically the paths is C:\Users\<USER>\Documents\Qlik\Sense\Apps, where <USER> is your actual Windows user name. If possible please provide a QVF file to show your issue in more detail.

[ToniKautto]

In addition all values loaded in Qlik are trimmed by default. There is no need to add the Trim() in this scenario.

Note the value is trimmed when loaded form source. Values are not automatically trimmed when returned from a function. SubField(' abc ', 'b', 1) for example will return a value with a leading blank space.

[vvvvvvizard]

Try where len(trim(value))>0 and Len(Date#("date",'YYYY-MM-DD'))>0