topic Re: Join tables in User interface in QlikView

Join tables in User interface

Tue, 07 Apr 2015 15:07:24 GMT

Hi All,

I have a question: is there a way to do a join between two tables in user interface? I know I can so it while scripting, but I need to create something more complicated.

Example:

Table1 (using alternate state)

John Doe

Richard Smith

Mary Guinness

Table2 (using alternate state 2 as they're the same records as before but a different selection)

John Doe

I'd like to create a table 3 where I'd like to do an inner join to keep records that are in table 1 but not in table 2.

Any ideas?

Thanks in advance

Diego

Re: Join tables in User interface

petter — Tue, 07 Apr 2015 15:55:42 GMT

Actually there are several ways of achieving it in QlikView... but it is not necessarily intuitive and it might not perform extremely well with a large number of records.

1) You can use CONCATENATE so both tables are considered as one QlikView logical table.

Should actually perform quite well when you do Set Expressions and use the set operators between

subsets of the same table * + - / (set operations) that corresponds to join operators ...

2) You can use a combination of Match, SubField and Concatenate - which might not perform so well with

large number of records.

Just to give you some idea of an expression that works in a sample app I made:

SubField( Concat( DISTINCT ID2 ,',', Num2) , ',' , Match( Num1 , $(=Concat( DISTINCT Num2, ',', Num2 ))) )

Re: Join tables in User interface

Tue, 07 Apr 2015 16:03:43 GMT

Wow thanks Petter, seems pretty far from my actual level... Gotta study what you said to undestand, but thanks for the hint

Re: Join tables in User interface

Wed, 08 Apr 2015 06:38:01 GMT

Hi Petter,

sorry to bother you. Can you help me with an example?

I have two tables (user interface, not in the script):

table1

-code

-name

table2

-code

-name

Both are based on the same table loaded in the script, using two alternate states.

How can I do what you suggested?

Thanks in advance

Re: Join tables in User interface

petter — Wed, 08 Apr 2015 06:52:00 GMT

In this scenario you could just use a Pivot Table without any logic. Just have code and name as dimensions and pivot the name to a column. You have to have name as an expression too like this:

only(name)

Re: Join tables in User interface

Wed, 08 Apr 2015 07:38:59 GMT

Ok maybe this is a silly question but.. How can I compare in the pivot fields coming from two different alternate state?

Re: Join tables in User interface

petter — Wed, 08 Apr 2015 08:24:43 GMT

You can do that in a Set Expression where you prefix the field name with the state name and use :: as a separator between them like this:

Sum( { <ID={"=State1::ID1=State2::ID1"} > } Sales )

Re: Join tables in User interface

Wed, 08 Apr 2015 09:42:13 GMT

ok you're doing a sum. But I'm not going to sum or aggregate, so I don't know how to do that..

Re: Join tables in User interface

petter — Wed, 08 Apr 2015 10:52:18 GMT

You have other non-numeric aggregations and pseudo-aggregations available:

Only() which could be termed as a pseudo-aggregation since it doesn't aggregate anything only pick the only value and the rest of the values have to be NULL.

Concat() which concatenates the numbers or strings into one long string with a chosen delimiter between the values.

I find the Concat() extremely handy also during development because I can have a peek at the actual values before I replace the Concat() with the aggregation I want. In a lot of instances it is exactly what I want as a result too.

If you for instance use Concat( name , ' / ') ... you will get something like 'John / Mary / Paul / Steven / Tom' if the field [name] contains 5 values ...

Re: Join tables in User interface

petter — Wed, 08 Apr 2015 11:00:20 GMT

Furthermore - if you are certain that your dimensions always will return one single value from a field and you dont enable any drill-down functionality you can use a field directly... That is the same as using the Only() function. Actually QlikView wraps the field in an Only()-function behind the scenes anyway if you don't do it yourself.

The advantage of wrapping the field in the Only() function is that you can use Set Expression as a part of it - so even if your dimensions doesn't fully specify so you get one value you can actually use the Set Expression to further qualify so that you get the single value you need.

Re: Join tables in User interface

Fri, 28 Aug 2015 22:22:00 GMT

Hi Peter,

I have a similar question and am hoping you can provide a bit of guidance.

If I have two alternate states with, again, the same table and make different selections in each alternate state, how can I see a table with results of the combined states. Here's an example. Is this possible?

Thank you.

Re: Join tables in User interface

Oleg_Troyansky — Fri, 28 Aug 2015 22:26:02 GMT

You need to use Set Analysis with the Boolean addition to get the sum of the two States. Something like this:

Sum( {State1+State2} Sales)

Something similar should also work for the original question in this thread.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense