topic Re: Combine values from Two Dimensions in the same table in QlikView

Combine values from Two Dimensions in the same table

sjhussain — Sat, 07 May 2016 12:05:03 GMT

‌Hello,

Hope all is well with everyone.

How can we combine values from two different dimension from while keeping the relationship.

We are working on a dashboard for GL - where we have 3 level of Accounts.

I have two dimensions from the same table, ACCTID_L2_DESC and ACCTID_CUST

As you can see COS in linked to 7 values in ACCTID_L2_DESC. What we want to do is to have COS in ACCTID_L2_DESC and when we select COS then it should like to the same 7 values in ACCTID_L2_DESC field.

Will appreciate if someone can assist?

Thanks.

Re: Combine values from Two Dimensions in the same table

swuehl — Sat, 07 May 2016 12:15:35 GMT

I am probably just not understanding what you want to achieve.

If you somehow add a value COS to ACCTID_L2_DESC, and select this single value, all other values in this field will be excluded (greyed out).

Re: Combine values from Two Dimensions in the same table

sjhussain — Sat, 07 May 2016 13:15:26 GMT

Stephen,

Right now COS is in the ACCTID_CUST Dimension and is linked to the other dimensions.

What we want to do is to have COS in ACCTID_L2_DESC and it should maintain the same links. Meaning that when we select COS it should display the 7 values link.

So when we are using set analysis then for COS it should add all the amounts related with ACCTID_L2_DESC

Hope this is clear?

Thanks.

Re: Combine values from Two Dimensions in the same table

swuehl — Sat, 07 May 2016 13:19:10 GMT

Not really. How do you want to create associations between field values within the same field?

Re: Combine values from Two Dimensions in the same table

sunny_talwar — Sat, 07 May 2016 13:38:23 GMT

Are you imagining something like this?

I used this script:

Table:

LOAD ACCID_CUST & '-' & ACCTID_L2_DESC as Hierarchy,

LOAD * Inline [

ACCID_CUST, ACCTID_L2_DESC

COS, Bussiness Trips

COS, COGS

COS, Commission/Business

COS, Misc.

COS, Personnel Cost

COS, Rent

Revenue, Revenues

COS, Utilities

];

and then to create the tree view list box, I selected Tree-View in the list box properties on the general tab

This might not be exactly what you wanted, but this might work.

Best,

Sunny

Re: Combine values from Two Dimensions in the same table

sjhussain — Sat, 07 May 2016 14:25:03 GMT

Stephen

What I want to do is the following.

Dimension: ACCTID_L2_DESC

Expression: sum(Amount)

Now for COS what should be displayed is the sum of all the amounts of the 7 values which are available like personnel cost, misc. etc

Hope this is mich clear now.

Thanks

Re: Combine values from Two Dimensions in the same table

rrsrini2907 — Sat, 07 May 2016 14:40:19 GMT

Hi Syed,

Do you want to create report as like below picture?

Regards,

Srini.

Re: Combine values from Two Dimensions in the same table

swuehl — Sat, 07 May 2016 14:57:02 GMT

Either use e.g. a pivot table with two dimensions, just like Srinivasan showed, or create a table that defines your new, single dimension with a total like:

LOAD ACCTID_L2_DESC, ACCTID_L2_DESC as NewDim

RESIDENT YourTable;

CONCATENATE

LOAD ACCTID_L2_DESC, 'TOTAL Revenue' as NewDim

RESIDENT YourTable

WHERE ACCTID_L2_DESC = ' Revenues';

CONCATENATE

LOAD ACCTID_L2_DESC, 'TOTAL COS' as NewDim

RESIDENT YourTable

WHERE ACCTID_L2_DESC <> ' Revenues';

Here you are creating multiple records per ACCTID_L2_DESC value, once for the detailed line and once to create an association for the total.

Re: Combine values from Two Dimensions in the same table

sjhussain — Sat, 07 May 2016 15:03:31 GMT

Sunny,

Thanks for the message. What we want is to have one list in which COS is there and it would have the sum of all the other values.

Same as u meantioned but we want COS in the same hierarchy as the others on ACCTID_L2.

Re: Combine values from Two Dimensions in the same table

rrsrini2907 — Sat, 07 May 2016 15:10:27 GMT

Hi Syed,

Try this:

GL_Details:

LOAD * Inline [

ACCID_CUST, ACCTID_L2_DESC, GL_Amount

COS, Bussiness Trips, 1200

COS, COGS, 500

COS, Commission/Business, 275

COS, Misc., 350

COS, Personnel Cost, 550

COS, Rent, 2500

Revenue, Revenues, 10000

COS, Utilities, 750

];

Concatenate (GL_Details)

LOAD ACCID_CUST,

ACCID_CUST as ACCTID_L2_DESC,

Sum(GL_Amount) as GL_Amount

Resident GL_Details

Group By ACCID_CUST;

Regards,

Srini.

Re: Combine values from Two Dimensions in the same table

sunny_talwar — Sat, 07 May 2016 16:02:23 GMT

Do you want this in a list box object or straight table, pivot table? I am confused as to what exactly you want