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: 
Not applicable

Select "impossible" values

When combining two sources in a QV application containing for example:

- Product ID, Product description
- Product ID, Product price

Then I would like to create a table with Product ID, Product Description, Product Price (for example look below) and have the ability to select those product ID's without a description or a price. Unfortunately I'm unable to select them from the table or create a listbox with this option.

Product ID, Product Description, Product Price
1 Coca Cola, 2,39
2 Fanta -
3 - 1,87

1 Solution

Accepted Solutions
Not applicable
Author

Hi Anja,

so far as I saw both of your tables are containing only two records and no record contains a NULL value.

What you got at your worksheet is the result of combining these records in a table.

You can build this table in your load script (join) and now you are able to ask for NULL values.

Good luck!

Rainer

View solution in original post

10 Replies
Not applicable
Author

if you can upload ur qvw, i will have a look

without which it is difficult to know what you want and what you have done

Not applicable
Author

I attached the QVW to this post. It's a easy example of somethign we would like to use a lot in our organization: create exception lists like in this case the ability to select all product ID's without a description or a price, but unfortunately you are unable to click the '-' in the table.

Not applicable
Author

Hey,

without looking at your qvw, i think the solution of your problem is the NullAsValue Function.

For example use the "NullAsValue Product Description, Product Price" in your script.

See also the documentation of qlikview.

Gruß
Christoph aus Franken 😉

Not applicable
Author

Thanks for your response, I've already tried adding NullAsValue *; to my script, but it still doesnt allow me to select them. I'm using v9.

Miguel_Angel_Baeyens

Hello Anja,

You may try a conditional where you check the length of value (although IsNull() function should work, I have had some issues under x64 versions), looking like

If(Len(Field) >0, Field))
will show only those values "not null" or not empty. But bear in mind that with some analysis, the information is still there, although you are not showing it.

Not applicable
Author

I want to do exactly the opposite, showing the NULL values and making them selectable. What I've tried is giving the NULL values in my source the value 'N/A' but this will work only for missing values in the individual source and not when combining fields from different sources in one QVW.

Not applicable
Author

Hi Anja,

so far as I saw both of your tables are containing only two records and no record contains a NULL value.

What you got at your worksheet is the result of combining these records in a table.

You can build this table in your load script (join) and now you are able to ask for NULL values.

Good luck!

Rainer

Not applicable
Author

Thanks Rainer! This would solve my problem but would also result in preloading a lot more data and anticipate all possible combinations users will want to make. Is there any way you can solve it in the applications itself (allowing our users to create their own exception lists instead of asking the developer to adjust the script)?

Anonymous
Not applicable
Author

Try using the "Select Possible" and "Select Excluded" functions. If you right click on the Product Price and press "Select Possible" the ProductDescription List will contain only those values that have a price. Then use "Select Excluded" on the Product Description and you will get a table that contains all values that are missing a Product Price. Mostly user education on these functions will be needed.