Replacing Null using If then and then filtering issue

[nadkalyan]

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

[nadkalyan]

Any suggestions?

[#3]

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

Just an idea

[fosuzuki]

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

[#5]

That should be the best way of doing it.

Philippe

[nadkalyan]

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?

[nadkalyan]

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?

[#8]

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

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

Philippe

[nadkalyan]

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