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

Setting a variable equal to the equivalent of Excel ""


Hello everyone.  This is my first post in quite a while.  I am trying to set a variable equal to the equivalent of what would appear in an Excel formula as "".  I have tried '', which isn't working for me.  I've also tried '<NULL>', which does not work.   Also, I ahve tried '*'.

Any ideas?  Thanks everyone

7 Replies
Not applicable
Author

Have you tried

Let vVariable = Null() ;

Anonymous
Not applicable
Author

I tried it, but Null(); isn't working either.  Here is the Excel formula that I am trying to replicate in QlikView.  (Please notice that there are named ranges in this formula):

=SUMIFS(FaceAmt,Disposition,"")

I am re-writing this in QlikView as:

=money(sum({$<Disposition = { $(varBlank) }>} [Face Amt Denied/Paid] ))

it is what to put in the section Disposition = {  } that I am having difficulty with.  Thanks

agomes1971
Specialist II
Specialist II

Hi,

LET yourvarname=null();


HTH


André Gomes

Anonymous
Not applicable
Author

Unfortunately, that isn't working

marcus_sommer

I'm not absolutely sure but I think a variable couldn't have NULL in qv. A statement like: let var = null(); deletes the variable. Why want you use a variable for this?

Have you tried expressions like these:

=money(sum({$<Disposition = { '' }>} [Face Amt Denied/Paid] ))

=money(sum({$<Disposition -= { '*' }>} [Face Amt Denied/Paid] ))

Other possibilities could be to transform these empty fieldvalues (qv interpretet this as NULL) into real values - have for example a look to NullAsValue.

- Marcus

Anonymous
Not applicable
Author

Thanks Marcus.  I will try your suggestions

Anonymous
Not applicable
Author

I ended up simply qualifying NULLS in my Load script using this:

if

(isnull(Disposition), 'NULL', Disposition) AS Disposition,

Then I can filter for NULL in my other scripts.  Thanks everyone.