Union 2 Fields, in expression

[Anonymous]

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.

[sunny_talwar]

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)

[Anonymous]

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.

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

Try this

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

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

[Anonymous]

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