Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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!

1 Solution

Accepted Solutions
MVP
MVP

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.

6 Replies
MVP
MVP

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.

MVP
MVP

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.

Not applicable

Re: Union 2 tables

Hi Petter

Thank you, it works.

Not applicable

Re: Union 2 tables

Hi Jagan

Works perfect.

Thank you.

Best Regards,

Max

oknotsen
Honored Contributor III

Re: Union 2 tables

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 .

May you live in interesting times!
MVP
MVP

Re: Union 2 tables

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