topic Re: Handling blank value in QlikView

Handling blank value

ajaykumar1 — Tue, 12 May 2015 10:40:57 GMT

Hi Everyone,

I need one help from someone.

I am getting some fields information in the design level with data model.But as per the client requirement i need to avoid the blank/null value in my report.

This is my requirement.

Input :

DeSc	Order	Line	Amount	charge cost
			2180	2155
123789 - Mobile	88338276	10	2180	0
-	88338276	10	0	2155

Output:

DeSc	Amount(Expression1)	charge cost(Expression2)
	2180	2155
123789 - Mobile	2180	2155

Here i need to develop the logic in design level instaed of touching the big data model.

Also am attaching the sample inpiut and output fiels as for reference.I hope your understood my requirement.

More Thanks,

Ajay

Re: Handling blank value

rubenmarin — Tue, 12 May 2015 10:47:34 GMT

Hi Ajay, you can change DeSc to a calculated dimension and check 'Supress when value is null', ie:

Aggr(If(Len(DeSc)>0, DeSc), DeSc)

In your sample xlsx there is a row with '-', and that's not a blank or null value.

Re: Handling blank value

anbu1984 — Tue, 12 May 2015 10:52:56 GMT

Check this app

Re: Handling blank value

ajaykumar1 — Tue, 12 May 2015 11:19:33 GMT

Thanks for Ruben and Anub for quick response.

I will check this logic in my main application and will revert with appropriate mark.

Ajay

Re: Handling blank value

Tue, 12 May 2015 12:35:47 GMT

I can see that the Desc column for both records is not the same. I would expect Qlikview to sum them up if they were the same. even tho the Account and line numbers are the same for both records but as they have a different description. they will be considered as seperate groups or records. I dont think you are looking to supress zeros or nulls but you are looking to join both records and sum numbers as they are for the same Account number.

There is a way to this is to peek on the previous record and check against account number , then ensure that the desc is the same. I guess it is a dirty data. I may be wrong. I Will give it a try

Re: Handling blank value

ajaykumar1 — Wed, 13 May 2015 06:01:20 GMT

Hi All,

I have applied the below logic in my new expression as part of requirement.But am unable to get the 'Expression total '. When am removing the Aggr function am getting the expression total and sum.

Expression 3 Logic :

sum(A) - sum(B)

Aggr(sum(charge cost),Order,Line))

Need help and we are using straight table.

More Thanks,

Ajay

Re: Handling blank value

ajaykumar1 — Wed, 13 May 2015 06:29:55 GMT

Hi All,

Any help from anyone?

More Thanks,

Ajay

Re: Handling blank value

er_mohit — Wed, 13 May 2015 07:34:39 GMT

Hi ajay

if you are using Straight table

then in properties got to expression tab on right corner there's a radio button tick on Sum of rows for that particular expression.

Thanks

Mohit

Re: Handling blank value

ajaykumar1 — Wed, 13 May 2015 08:00:10 GMT

THanks Mohit.

But am looking for 'expression total' not the 'sum' value.

Any way my issue seems resolved by using the below formulae.

sum(A) - sum(B)

sum(Aggr(sum(charge cost),Order,Line)))

Thanks,

Ajay