Create Key with inconsistent Field

[#1]

Hallo Everybody:)

I want to ,better i have to create a key with an inconsistent field. Normaly this field is an ID, but our Database isn't complete.

Means,i have 3 Tables involving this field, let's call it ID. The first Table contains every value of ID.

The second only a part and the third includes the rest. So u can say :   

count(distinct Table2.ID) + count(distinct Table3.ID) = count(distinct Table1.ID)

I want to connect them, so that when i choose a field from Table1, the ID in Table2 or Table3 is choosen too.

Or backwards, when i choose an ID from Table2, the ID in Table1 should be coosen too.

Pls dont ask me why^^

I'm working on a demonstration Project and thats why i get an example Database;(

So i have to deal with that...

What can i do? When i simply connect them by setting this column name in every Table to ID at import, QV makes Table2 and Table3 loosely coupled.

I tried to create an connection Table, like:

Table1.ID	Table2.ID	Table3.ID
1	1	-
2	-	2
3	3	-
4	4	-
5	5	-
6	-	6
7	-	7

with following Script:

ID_ALL:

LOAD distinct Table1.ID as ID resident Table1;

LEFT JOIN (ID_ALL) LOAD Table2.ID resident Table2 where Table2.ID=ID;

LEFT JOIN (ID_ALL) LOAD Table3.ID resident Table3 where Table3.ID=ID;

This cant be executed because the Field ID cannot be found...

Do i need more Fields for join? Is it senseless to join like this? Its not my first join...but i dont get it work...

I also read about ApplyMap, could this function help?

Or also to less fields?

Any idea?

Thx for reading;)

Sarah

[Gysbert_Wassenaar]

Leave out the where clauses. Qlikview joins tables on matching field names. You cannot specify the field names for the join like in SQL.

ID_ALL:

LOAD distinct Table1.ID as ID resident Table1;

LEFT JOIN (ID_ALL) LOAD Table2.ID as ID resident Table2;

LEFT JOIN (ID_ALL) LOAD Table3.ID as ID resident Table3;

Note, the above statement doesn't actually do anything since you don't load any other fields from Table2 and Table3, so nothing gets joined with Table1. You probably want something like LEFT JOIN (ID_ALL) LOAD *, Table2.ID as ID resident Table2;

[#3]

Thanks a lot for all the posts:)

This is more interesting to me. Funny is that i always join with where condition^^

And it works...Does QV ignores the condition, when it would join with the same fields?

I changed my script to LEFT JOIN (ID_ALL) LOAD *, Table2.ID as ID resident Table2;

But i dont know if it works because im running out of virtual and/or physical Memory:(

One million Rows seem to be too much to join or is there a way to tell QV do it partly or sth?

Any other Solutions?

[#4 · Accepted solution]

Ok, i get it with ApplyMap. This can be executed without running out of virtual and/or physical memory(in this/my case).

This is the Script:

I simplify the names of the ID to ID1^^

ID1_Map1:

Mapping LOAD DISTINCT Table2.ID1,Table2.ID1 as MapID1 resident Table2;

ID1_Map2:

Mapping LOAD DISTINCT Table3.ID1,Table3.ID1 as MapID12 resident Table3;

ID1_DISTINCT:

LOAD DISTINCT ID1 resident Table1;

ID1_TMP:

LOAD *,MapID1 as MapID2,ApplyMap('ID1_Map1',MapID1,null()) resident ID1_DISTINCT;

ID1_ALL:

LOAD *,ApplyMap('ID1_Map2',MapID2,null()) resident ID1_TMP;