Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Join tables in User interface

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

11 Replies
MVP
MVP

Re: Join tables in User interface

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 ))) )

Not applicable

Re: Join tables in User interface

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

Not applicable

Re: Join tables in User interface

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

MVP
MVP

Re: Join tables in User interface

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)

Not applicable

Re: Join tables in User interface

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

MVP
MVP

Re: Join tables in User interface

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 )

Not applicable

Re: Join tables in User interface

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

MVP
MVP

Re: Join tables in User interface

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 ...

MVP
MVP

Re: Join tables in User interface

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.