topic Problem with expression where field is blank in QlikView

Problem with expression where field is blank

gerhardl — Tue, 26 Apr 2011 12:39:50 GMT

Hi,

I have a nar chart with 2 expressions. The first expression shows me the average number of "days since last movement" on the account where the "Transaction Amount" Field is not blank. The second expression should show me the exact same thing, but where the Transaction Amount field IS blank. The first one works 100%, but the second expression returns nothing. Can someone see what is wrong with my second expression?

Expression 1: Avg({1<[Trans Amount]-={} >} [Days since last movement])

Expression 2: Avg({1<[Trans Amount]={} >} [Days since last movement])

Thanks,

Problem with expression where field is blank

Anonymous — Tue, 26 Apr 2011 12:45:58 GMT

Try to create a flag in your load script:

if(isnull(YourField),'Y','N') as NullFlag

Change your expression to :

Avg({<NullFlag={'Y"}>} [Days since last movement])

Regards,

Dinesh.

Problem with expression where field is blank

gerhardl — Tue, 26 Apr 2011 12:52:52 GMT

I'm not sure where I would put that in, as there techically are no blanks in this field...

What I do is load an excel spreadsheet with account numbers, then I load daily transaction extracts with the following WHERE clause:

WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No])

So only transactions where the account numbers match an account number in the very first list will be loaded.

So the "Trans Amount" field will only be blank if this person has not yet made a purchase, i.e. he is not in the transaction extracts.

Did I confuse the matter now?

Problem with expression where field is blank

gauravkhare — Tue, 26 Apr 2011 13:12:03 GMT

Hi,

KIndly try inserting only single quotes like ''

Problem with expression where field is blank

gerhardl — Tue, 26 Apr 2011 13:14:26 GMT

Hi Gaurav,

I have tried both single and double quotes, with and without spaces between them - nothing seems to work...

Problem with expression where field is blank

IAMDV — Tue, 26 Apr 2011 13:20:08 GMT

Gerhardl - Have you tried using the Null() function within your second expression?

Problem with expression where field is blank

gerhardl — Tue, 26 Apr 2011 13:23:14 GMT

Hi DV,

No - please could you show me how? On this expression

Avg({$<[Trans Amount]={} >} [Days since last movement])

Thanks,

Problem with expression where field is blank

Tue, 26 Apr 2011 13:24:38 GMT

For handling nulls the best thing u can do is to get the length.

U can try this:

If(Len(TransInYearMonth<0 ),1,0) as NullFlag

and use this in your expression:

-1 <NullFlag= {"1"}>

Thanks,

Srihari

Problem with expression where field is blank

gerhardl — Tue, 26 Apr 2011 13:26:21 GMT

Alternatively, can i not exclude a certain selection?

So I would say

Avg( [Days since last movement]) but EXCLUDE where [Trans Amount]-={}

How can I do that???

Thanks,

Problem with expression where field is blank

Tue, 26 Apr 2011 13:27:44 GMT

Hi,

Check this below Expression helps you.

Avg({<[Days since last movement] = {"=len([Trans Amount)=0"}>} [Days since last movement])

Untested expression.

- Sridhar

Problem with expression where field is blank

gerhardl — Tue, 26 Apr 2011 13:31:28 GMT

No I'm afraid that also returns nothing...

Problem with expression where field is blank

lgz — Tue, 26 Apr 2011 13:32:48 GMT

Hi, How about using len() function? You can try this: avg( {1} [Days since last movement]) ) Hope this helps

Problem with expression where field is blank

gerhardl — Tue, 26 Apr 2011 13:40:03 GMT

I managed to figure it out.

I created a bookmark where all accounts with a transaction are selected. Then I exclude the bookmark in my expression:

Avg ({1-Bookmark01} [Days since last movement])

Thanks for everyone's input. What a mission that was.

Regards,

Problem with expression where field is blank

Tue, 26 Apr 2011 13:43:52 GMT

Hi,

Try like this way

Normal 0 false false false EN-US X-NONE X-NONE

Avg({$<[Trans Amount]={''=len(Trans Amount)=0"} >} [Days since last movement])

Normal 0 false false false EN-US X-NONE X-NONE

Regards,

Sampath Kumar . G

Problem with expression where field is blank

Tue, 26 Apr 2011 13:45:09 GMT

Hi,

Look at the attached application.

If this also doesn't solve the your problem. post the sample data with your required output.

- Sridhar