CODEWITHSUNDEEP
sqlloopsjoinconcatenationqlikview
mattuch
mattuch

Reputation: 21

Adding same table to two different tables in QlikView - loop error

I'm a new QlikView user. I've got a prepared data (table Alpha, Beta and Gamma, based on SQL selects), to which I needed to add a new one (Delta- also SQL select). With all of the above I want the make a whole 'Alphabet'.

Image showing current tables and the wanted result -> here

There's no definied connection between Alpha and Beta or Gamma (so as far as I know it is concatenation). Everything works fine when I'm left joining Delta with Beta. But I need this 'E' column to be joined to Gamma table aswell (Bkey1 = Bkey2 = Bkey).

So at this point, in order to add Delta table, I type something something like this:

(...)
LOAD
Bkey as Bkey1,
E
;
SQL(...)
;

And as I said it works fine, but now it's time to join Delta with Gamma, so I paste the same SQL Select again and just changing the alias for Bkey:

(...)
LOAD
Bkey as Bkey2,
E
;
SQL(...)
;

And that's when I get the loop error thing.

One or more circular references have been discovered in the structure of your database. They can cause ambiguous results and therefore should be avoided. QlikView will remove those circular references by setting one or more tables as loosely (by others). This setting can be changed after running the script in the property page of the document

I don't really know what to do. The only solution I see right now is to UNION the Beta and Gamma table together and get the Bkey in one column only. But I don't really know if it will be the most effective way. Besides I hardly believe that QlikView cannot do such simple thing as adding just one additional column (E).

As I said before, I'm a newbie at QlikView, so far I've been working just with SQL and VBA, so I surely have some lack of knowledge in this manner. I will apprecieate all answers. Thanks in advance.

Additional info: AKey and Bkey are consistent but in Beta there might be more rows than in Gamma and vice versa.

Upvotes: 0

Views: 376

Answers (1)

seebach
seebach

Reputation: 621

I would concatenate Gamma and Beta, concatenate is Qliks version of Union. I however assume that Akey and Bkey are consitent across both tables and there relations. I'm also adding a Source field so I can distinguish Beta and Gamma. You're not gaining anything by joining the tables in SQL. Union on the Qlik side is also very fast, and does not require fields to be named the same. A linked table, is functionally the same as join in SQL.

In qlik that would be the following:

Alpha:
LOAD *;
SQL SELECT Akey,A,B FROM Alpha;

Beta:
LOAD 'Beta' as Source,*;
SQL SELECT Akey,Bkey,C FROM Beta;

Concatenate (Beta):
LOAD 'Gamma' as Source, *;
SQL SELECT Akey,Bkey,D FROM Gamma;

Delta:
LOAD *;
SQL SELECT Bkey,E FROM Delta;

Upvotes: 0

Related Questions