Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

thnkgreen
Contributor

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

Re: Setting a variable equal to the equivalent of Excel ""

Have you tried

Let vVariable = Null() ;

thnkgreen
Contributor

Re: Setting a variable equal to the equivalent of Excel ""

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
Valued Contributor

Re: Setting a variable equal to the equivalent of Excel ""

Hi,

LET yourvarname=null();


HTH


André Gomes

thnkgreen
Contributor

Re: Setting a variable equal to the equivalent of Excel ""

Unfortunately, that isn't working

Re: Setting a variable equal to the equivalent of Excel ""

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

thnkgreen
Contributor

Re: Setting a variable equal to the equivalent of Excel ""

Thanks Marcus.  I will try your suggestions

thnkgreen
Contributor

Re: Setting a variable equal to the equivalent of Excel ""

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.


Community Browser