topic Re: Union 2 tables in App Development

Union 2 tables

Sun, 08 Jan 2017 10:13:52 GMT

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

petter — Mon, 09 Jan 2017 00:36:06 GMT

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

jagan — Mon, 09 Jan 2017 05:47:39 GMT

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.

Re: Union 2 tables

Mon, 09 Jan 2017 08:31:54 GMT

Hi Petter

Thank you, it works.

Re: Union 2 tables

Mon, 09 Jan 2017 08:33:09 GMT

Hi Jagan

Works perfect.

Thank you.

Best Regards,

Max

Re: Union 2 tables

oknotsen — Mon, 09 Jan 2017 09:15:29 GMT

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 .

Re: Union 2 tables

jagan — Mon, 09 Jan 2017 13:35:23 GMT

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