Union 2 tables

[#1]

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!

[petter · Accepted solution]

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.

[jagan]

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.

[#4]

Hi Petter

Thank you, it works.

[#5]

Hi Jagan

Works perfect.

Thank you.

Best Regards,

Max

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

[jagan]

Hi,

If you got the answer close this thread by giving Correct Answer.

Qlik Community Tip: Marking Replies as Correct or Helpful

Regards,

Jagan.

Qlik Community Tip: Marking Replies as Correct or Helpful