6 Replies Latest reply: Jan 9, 2017 8:35 AM by jagan mohan rao appala

# Union 2 tables

Hello

I am trying to union 2 dimensions and count each value in each dimension, showing the counts in one table (as I understood it's possible to visualize it only in charts and pivot table).

Here is the script I'm using:

----------------------------------------------------------------------------------------------

Dimension:

ValueList('Passed','Failed','B','N','?')

Measure:

If(

ValueList('Passed','Failed','B','N','?')=

'Passed',Count([Technical Validation]),

if(ValueList('Passed','Failed','B','N','?')=

'failed',Count([Technical Validation]),

if(ValueList('Passed','Failed','B','N','?')=

'B',Count(CalcBleed),

if(ValueList('Passed','Failed','B','N','?')=

'N',Count(CalcBleed),

Count(CalcBleed)))))

---------------------------------------------------------------------------------------------

'Passed','Failed' - 2 values in [Technical Validation] column

'B','N','?' - 3 values in [CalcBleed] column

What I get is a pivot table that counts for each value all the rows in the Data (1688): Attached image with pivot table of the calculation (union).

Can someone help me with the script please?

Thanks!

• ###### Re: Union 2 tables

This expression could be a solution for you:

Pick( Match ( ValueList('Passed','Failed','B','N','?') ,'Passed','Failed','B','N','?') ,
,Count( {<[Technical Validation]={'Passed'}>} [Technical Validation])
,Count( {<[Technical Validation]={'Failed'}>}  [Technical Validation])
,Count( {<CalcBleed={'B'}>} CalcBleed)
,Count( {<CalcBleed={'N'}>} CalcBleed)
,Count( {<CalcBleed={'?'}>} CalcBleed)
)

I would assume that the value in the two columns/fields could be overlapping so unifying them into a single field in a load script would be tricky. I don't know the nature of your data and table so I could only guess.

• ###### Re: Union 2 tables

Hi Petter

Thank you, it works.

• ###### Re: Union 2 tables

Hi,

Is Technical Validation and CalBleed are in same table?  You didn't used any filters using setting analysis, so based on your data you are getting the same values for all the ValueList() values.  ValueList() values do not have any link with your expressions, so you have to use set analysis to filter the data.

For example: Count( {<[Technical Validation]={'Passed'}>} [Technical Validation])  -- This counts only Technical Validation with Passed values.

Hope this helps you.

Regards,

Jagan.