Add value to all dimension values (also null values)

[pvanderkolk]

Hi all,

I have two dimensions. The first, 'Place' contains only three values (A,B and C) and the other, 'Number' has multiple values. This is how the table looks like for Number 1:

Place	Number	Total
		€ 420,92
A	1	€ 265,11
B	1	€ 95,40
-	1	€ 60,60

What I want to see, are all values of Place per Number. I know when marking 'Show all dimension values' in the tab Dimensions, I'll get the following result:

Place	Number	Total
		€ 420,92
A	1	€ 265,11
B	1	€ 95,40
-	1	€ 60,60
C	-	€ 0,00

Here's the thing: when my first dimension Place has a null value, I want to split the costs proportionally to all dimension values of Place. So all the values, including Place C, should have an addition of 60,60/3. Whatever I do, Place C remains 0. Next to this, I also would like to see that the number next to Place C also shows 1 instead of '-'.

The desired output should then be:

Place	Number	Total
		€ 420,92
A	1	€ 285,31
B	1	€ 115,60
C	1	€ 20,20

Can anybody help me with resolving this issue?

Thanks a lot!

[StarinieriG]

Hi,

try with something like this:

TABLE:
LOAD
Place,

Number,

Total

FROM File;

NoConcatenate
OUTPUT_temp:
LOAD * Resident TABLE Where Len(Trim(Place))>0;

Left Join (OUTPUT_temp)
LOAD
Total AS Total_add
Resident TABLE
Where IsNull(Place)
;

Left Join (OUTPUT_temp)
LOAD
Count(DISTINCT Place) as Count
Resident OUTPUT_temp
;

Concatenate (OUTPUT_temp)
LOAD *,
0 as Total_add,
1 as Count
Resident TABLE Where Len(Trim(Place))=0;

DROP Table TABLE;

NoConcatenate
OUTPUT:
LOAD
Place,
If(IsNull(Number),1,Number) AS Number,
(Total+Total_add/Count) AS Total
Resident OUTPUT_temp
Where IsNull(Place)=0
;

DROP Table OUTPUT_temp;

[pvanderkolk]

Hi,

Thanks for the reply. Unfortunately it still only shows 'A' and 'B' from dimension 'Place'. 'C' also needs to be visible with a third of what the null value presented (i.e. 10). Do you have an idea what we are missing now?

[StarinieriG · Accepted solution]

Hi,

see file attached.

I used your example inside this qvw and it seems to work, maybe you have something different in your real data (?)

[pvanderkolk]

Hi,

Yes, that's true. But dimension value C has a 0 value in your sample, while it actually has a NULL() value. That's why I still can't see the value and you can. Do you perhaps know a workaround for this issue?

[StarinieriG]

Probably you could modify that field inside the script as below

If(IsNull(Total),0,Total) as Total

[pvanderkolk]

Hi,

It actually works in the test data. Unfortunately not in my real data. When I make a selection in dimension field Number and select '1', Place 'C' is greyed out already. It just doesn't know this combination of the two dimensions. Maybe this is resolvable by creating a cartesian product. I'll try this and let you know.

[Brett_Bleess]

Pim, do not forgot to give Giulia credit for the help by using the Accept as Solution button on any of his posts that helped you move things forward.  If you do end up doing something different, consider posting that and mark it.  If you get further information, leave an  update, and we'll see if anyone else has any further ideas, but you may want to consider attaching QVW with what you have next time, as I think it will likely  help you get more answers as folks will be able to see the data model and everything, these are tricky to figure out otherwise.

Regards,
Brett

[pvanderkolk]

Hi Brett,

Unfortunately the great help of Giulia didn't result in the answer I wanted to have. However, it was the solution the example needed so I will mark her answer as correct. 

I'm afraid attaching a QVW isn't an option, but I can add a QVW of the Excel output the next time.

Regards,

Pim