topic Re: Union 2 Fields, in expression in QlikView

Union 2 Fields, in expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

Say I have a table:

LOAD * INLINE [test1, test2

1, 2

2, 3

2, 4];

I want a count(distinct) of a union between test1 and test2. A count(distinct) on test1 gives me 2, and test3 gives me 3. But if they're unioned the count(distinct) would be 4.

Is it possible to achieve this within an expression?

I will attach a qlikview so you guys can see this.

Re: Union 2 Fields, in expression

sunny_talwar — Thu, 05 Apr 2018 13:35:25 GMT

May be this

=Count(DISTINCT Test2) + Count(DISTINCT {<Test -= {$(=Concat(DISTINCT Chr(39) & Test2 & Chr(39), ','))}>} Test)

Alternatively, this should also work

=Count(DISTINCT {<Test2 -= {$(=Concat(DISTINCT Chr(39) & Test & Chr(39), ','))}>} Test2) + Count(DISTINCT Test)

Re: Union 2 Fields, in expression

Anonymous — Thu, 05 Apr 2018 13:47:49 GMT

Hi Sunny,

This does work I believe, however when I use this with a sample of my actual data (9 million records) the performance suffers greatly -to the point where qlik freezes totally. I appreciate the idea but I'm not sure it will work.

The fields I'm dealing with are also alphanumeric

Any other ideas? It's no problem if qlik is limited to the doing this within the load statement, I can adjust. I was just hoping there was a function I didn't know that would bring 2 fields together in an expression.

Re: Union 2 Fields, in expression

sunny_talwar — Thu, 05 Apr 2018 13:50:00 GMT

I initially thought that this should work... but I am not sure why this isn't working

=Count(DISTINCT Test2) + Count(DISTINCT {<Test = e(Test2)>} Test)

or this

=Count(DISTINCT {<Test2 = e(Test)>} Test2) + Count(DISTINCT {<Test = e(Test2)>} Test)

Let me try few other options

Re: Union 2 Fields, in expression

sunny_talwar — Thu, 05 Apr 2018 13:51:21 GMT

Try this

=Count(DISTINCT Test2) + Count(DISTINCT {<Test -= p(Test2)>} Test)

=Count(DISTINCT {<Test2 -= p(Test)>} Test2) + Count(DISTINCT Test)

Re: Union 2 Fields, in expression

Anonymous — Thu, 05 Apr 2018 13:56:28 GMT

Nailed it! I'll have to look into this p() function, I have not seen this. Thanks for the help Sunny!