Need help with Len(Trim(Category))

[urbanfaces]

I have a load script that had dash or -. I want to exclude them from the load script. I know I can do it within the app, but I like to do it on the load script.

Len(Trim(Category))=’-‘ works, but I need the opposite     Len(Trim(Category))<>'-' The opposite does not work.

Any help will be great.

[henrikalmen]

Your expression seems to be wrong, the len() function returns a number.

You are probably looking for something like len(trim(replace(Category, '-','')))=0
( >0 for the opposite)

[urbanfaces]

Thank you so so much. But I want it not to add any row that the category is '-'

[henrikalmen]

LOAD ' RESIDENT [yourtable] where len(trim(replace(Category, '-','')))>0

[urbanfaces]

I keep on getting ')' expected error

[henrikalmen]

Can you post the script that you are writing? (The relevant part where you are performing this load with this filter?)

[urbanfaces]

 I think the issue is because it it null but qlik changes it to dash

[henrikalmen]

I don’t believe that’s your issue. The replace() I used compensates for it so that any dash values (-) are replaced by “nothing” (null).

[henrikalmen]

I should probably ask: are you aware of how null “values” work and what they are? I’m starting to think that maybe you are trying to get rid of things that just are not there… But it’s hard to know without knowing anything about the data you have.

[marcus_sommer]

By existing records you could check any field-value if it has a real NULL per isnull() or is EMPTY per len(trim()) = 0 or having a value per len() >= 1 respectively a certain value, like: value = 'MyValue' and including or excluding it from the load.

If records don't exists because a certain date/product has no sales/budgets or similar you couldn't exclude anything else you will need an reverse approach and populating these records.

Beside this by any kind of join/associations of tables you may create NULL which are visualized in all views with the dash '-' sign. They are not directly accessible in any way else they need to be addressed with population-measurements within the data-model.

Further important to know: NULL isn't an error itself else it has beside some disadvantages also some benefits. A very good explanation to all this stuff could you find here:

NULL handling in QlikView - Qlik Community - 1484472