Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
urbanfaces
Contributor III
Contributor III

Need help with Len(Trim(Category))

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.

Labels (2)
9 Replies
henrikalmen
Specialist
Specialist

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
Contributor III
Contributor III
Author

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

henrikalmen
Specialist
Specialist

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

urbanfaces
Contributor III
Contributor III
Author

I keep on getting ')' expected error

henrikalmen
Specialist
Specialist

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

urbanfaces
Contributor III
Contributor III
Author

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

henrikalmen
Specialist
Specialist

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

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