topic Re: Replacing Null using If then and then filtering issue in QlikView

Replacing Null using If then and then filtering issue

nadkalyan — Mon, 06 Aug 2012 18:43:16 GMT

Hi,

I have Data in this format

TotalSpendBusinessUnit	DelinquencyBusinessUnit	Deliquent	Total Spend
A&E-PS	-	0	234,307
A&E-PS	A&E-PS	14,778	28,513
-	A&E-PS	3,837	0

I have created a calculated Dimension to replace Null Values with data in any one of the columns as

BusinessUnit = if (isnull(TotalSpendBusinessUnit), DelinquencyBusinessUnit, TotalSpendBusinessUnit)

This is how data looks like with Business Unit Column

TotalSpendBusinessUnit	DelinquencyBusinessUnit	Business Unit	Deliquent	Total Spend
-	A&E-PS	A&E-PS	3,837	0
A&E-PS	A&E-PS	A&E-PS	14,778	28,513
A&E-PS	-	A&E-PS	0	234,307

When I click on Business Unit Filter selecting 'A&E-PS' based on above calculated dimension, the above table is filtered as only line that has 'A&E-PS' populated in all the dimensions as below -

TotalSpendBusinessUnit	DelinquencyBusinessUnit	Business Unit	Deliquent	Total Spend
A&E-PS	A&E-PS	A&E-PS	14,778	28,513

How can I strucuture my above table to show all the lines of A&E-PS?

Any assistance would be a big help!!

Thanks

Kalyan

Re: Replacing Null using If then and then filtering issue

nadkalyan — Tue, 07 Aug 2012 14:47:09 GMT

Any suggestions?

Re: Replacing Null using If then and then filtering issue

Tue, 07 Aug 2012 19:01:42 GMT

why don't you add a rowno() during your load statement and use it to display the information.

Just an idea

Re: Replacing Null using If then and then filtering issue

fosuzuki — Tue, 07 Aug 2012 19:30:49 GMT

Can't you create the Business Unit dimension in Script?

Re: Replacing Null using If then and then filtering issue

Tue, 07 Aug 2012 19:33:19 GMT

That should be the best way of doing it.

Philippe

Re: Replacing Null using If then and then filtering issue

nadkalyan — Tue, 07 Aug 2012 19:36:00 GMT

I may be not that familiar with Loading Scripts but 'TotalSpendBusinessUnit' and "DelinquencyBusinessUnit' are both coming from 2 different tables and they are loaded in 2 different tabs in Qlikview joined by a dimension key. How can I load calculated BusinessUnit in the script?

Re: Replacing Null using If then and then filtering issue

nadkalyan — Wed, 08 Aug 2012 18:13:00 GMT

Also if I do same cacluation for a list box, and select any one Business Unit it still gives me same result. I think this is the way Qlikview handles Nulls to not populate even using Outer Joins. Any suggestions?

Re: Replacing Null using If then and then filtering issue

Wed, 08 Aug 2012 18:21:23 GMT

you could use the lookup () function in your load script

if (isnull(TotalSpendBusinessUnit) , lookup ( DeliquencyBusinessUnit ),TotalSpendBusinessUnit ) as BusinessUnit.

Philippe

Re: Replacing Null using If then and then filtering issue

nadkalyan — Thu, 09 Aug 2012 13:54:21 GMT

I have added this to the Load script but still same results. I have added following also to Load script and i get same results -

Not sure how Qlikview handles Null Dimensions.

Kalyan