topic Re: How to combine different dimension in an object in QlikView

How to combine different dimension in an object

nayanqlik — Wed, 25 Nov 2020 16:16:04 GMT

For simplicity, I have the 2 tables below in my front end (Table1 & Table 2). The one is Sales by Manager whereas the other is Survey Count by Survey Manager. The values in the Manager and Sales Manager fields are the same person. I want to create a table in the front end where Manager and Survey Manager are linked. (see below table Correct).

However, my table is not displaying correct (see table marked Incorrect)

Please note, I don't want to link it in the backend as it has implications on other parts of the model.

So, can this be solved in the frontend only.

Your assistance is appreciated.

Kind regards

Nayan

Table 1

Manager	Sales
A	20
B	30
C	60

Table 2

Survey Manager	Survey Count
A	6
B	10
C	15

Correct

Sales Manager	Sales Manager Survey	Sales	Survey Count
A	A	20	6
B	B	30	10
C	C	60	15

Incorrect

Sales Manager	Sales Manager Survey	Sales	Survey Count
A	A	20	6
A	B	20	10
A	C	20	15
B	A	30	6
B	B	30	10
B	C	30	15
C	A	60	6
C	B	60	10
C	C	60	15

Re: How to combine different dimension in an object

sunny_talwar — Tue, 13 Mar 2018 14:37:37 GMT

May be just do this

If([Manager] = [Survey Manager], Sum(Sales))

If([Manager] = [Survey Manager], Sum([Survey Count]))

Re: How to combine different dimension in an object

nayanqlik — Tue, 13 Mar 2018 14:38:55 GMT

Hi Sunny

Thank you for your quick response. I will try and let you know.

Kind regards

Nayan

Re: How to combine different dimension in an object

sunny_talwar — Tue, 13 Mar 2018 14:39:41 GMT

Check the attached

Re: How to combine different dimension in an object

Digvijay_Singh — Tue, 13 Mar 2018 14:45:00 GMT

May be try like this -

Re: How to combine different dimension in an object

nayanqlik — Tue, 13 Mar 2018 14:54:56 GMT

Hi Sunny

It worked. Another twist. I have a lower level dimension. Employee which falls under Manager and Survey Employee falling under Survey Manager.

So will the formula be

If([Manager] = [Survey Manager] or [Employee] = [Survey Employee], Sum(Sales))

If([Manager] = [Survey Manager] or [Employee] = [Survey Employee],, Sum([Survey Count]))

Kind regards

Nayan

Re: How to combine different dimension in an object

sunny_talwar — Tue, 13 Mar 2018 14:56:17 GMT

May be using and instead of or....

Re: How to combine different dimension in an object

Nagaraju_KCS — Tue, 13 Mar 2018 14:56:49 GMT

HI,

why don't you make associate 2 tables with Manager.

Table1:

LOAD * INLINE [

Manager, Sales

A, 20

B, 30

C, 60

];

Table2:

LOAD *, [Survey Manager] as Manager;

LOAD * INLINE [

Survey Manager, Survey Count

A, 6

B, 10

C, 15

];

Then you can use simple expression.

Sum(Sales)

sum(Survey Count)

Re: How to combine different dimension in an object

nayanqlik — Tue, 13 Mar 2018 14:57:26 GMT

Hi Digvijay

Your formula worked too. See my response to Sunny, regarding a lower level of staff. I have an Employee which falls under Manager and Survey Employee falling under Survey Manager.

Should i apply similar formula to the one you have given for manager.

Kind regards

Nayan