Handling blank value

[ajaykumar1]

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

[rubenmarin]

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.

[anbu1984]

Check this app

[ajaykumar1]

Thanks for Ruben and Anub for quick response.

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

Ajay

[#5]

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

[ajaykumar1]

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

[ajaykumar1]

Hi All,

Any help from anyone?

More Thanks,

Ajay

[er_mohit]

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

[ajaykumar1]

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