Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
pvanderkolk
Partner
Partner

Add value to all dimension values (also null values)

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:

PlaceNumberTotal
  € 420,92
A1€ 265,11
B1€ 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:

PlaceNumberTotal
  € 420,92
A1€ 265,11
B1€ 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:

PlaceNumberTotal
  € 420,92
A1€ 285,31
B1€ 115,60
C1€ 20,20

 

Can anybody help me with resolving this issue?

Thanks a lot!

Labels (2)
1 Solution

Accepted Solutions
StarinieriG
Partner
Partner

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 (?)

View solution in original post

8 Replies
StarinieriG
Partner
Partner

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
Partner
Partner
Author

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
Partner
Partner

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 (?)

View solution in original post

pvanderkolk
Partner
Partner
Author

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
Partner
Partner

Probably you could modify that field inside the script as below

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

pvanderkolk
Partner
Partner
Author

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
pvanderkolk
Partner
Partner
Author

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