topic Issue in join Help me to resolve. in QlikView

Issue in join Help me to resolve.

xavieravinson — Wed, 10 Sep 2014 11:46:56 GMT

I have two tables one with unique key and another table this key has multiple records now i have to join the unique records based on the primary key please help me to resolve this issue.

Thanks in Advance

Re: Issue in join Help me to resolve.

AbhijitBansode — Wed, 10 Sep 2014 11:50:22 GMT

First Load table which has multiple records for single key.

Left Join this table with the Unique Key Table.

eg.

Load

ProductId

From SecondTable;

Left Join

Load

ProductId,

Group

From FirstTable;

Re: Issue in join Help me to resolve.

deepakqlikview_123 — Wed, 10 Sep 2014 11:53:59 GMT

Table1:

load

field1,

field2

from.....

table2:

load field1

resident table1;

left join(Table2)

field1,

field3

from......

hope this helps u

Re: Issue in join Help me to resolve.

Wed, 10 Sep 2014 12:11:03 GMT

Hi,

If I consider the two tables you have: table1 (with Primary Key) and table2 (child records), you may go for:

FinalTable:

noconcatenate

load * resident table1;

left join

load * Resident table2;

But the above gives you all the records irrespective wether child exists or not for Primary keys in table1. While code below

FinalTable:

noconcatenate

load * resident table1;

join

load * Resident table2;

gives only the matching records in both tables

Re: Issue in join Help me to resolve.

Colin-Albert — Wed, 10 Sep 2014 12:20:37 GMT

Do you need to join the two tables?

Just load the two tables separately and let QlikView associate the data on the unique key.

You just need to make sure the unique key has the same name on both tables.

Re: Issue in join Help me to resolve.

xavieravinson — Wed, 10 Sep 2014 12:27:18 GMT

No I have to join both the tables. both has different fields and the relation is primary key.

for example

table1

key f1 f2 f3

1 a b c

2 a b c

table 2

f4 f5 f6 f7

1 x y z

1 y z x

2 x k m

2 e s g

based on key and f4 i have to join.......

--------------------

Thanks for your reply sir

Re: Issue in join Help me to resolve.

Colin-Albert — Wed, 10 Sep 2014 12:31:35 GMT

You just need to rename column f4 as key in table2, then the tables will be associated in QlikView. You do not need to use join.

Re: Issue in join Help me to resolve.

veeranj — Wed, 10 Sep 2014 12:42:06 GMT

Hi ,

LOAD * INLINE [

key,f1,f2,f3

1, a, b, c

2, a, b, c

];

JOIN

load F1 AS key,F2,F3,F4

INLINE [

F1, F2, F3, F4

1, x, y, z

1, y, z, x

2, x, k, m

2, e, s, g

];

Thanks,

Anjee

Re: Issue in join Help me to resolve.

Colin-Albert — Wed, 10 Sep 2014 12:50:58 GMT

You do not need to join the tables, just rename the fields and load the data into separate tables.

The associative logic in QlikView will create the links between the tables automatically based on the field names. No joins!

table1:

LOAD

key,

f1,

f2,

from .... ;

table2

load f4 AS key,

f5,

f6,

from .... ;

Re: Issue in join Help me to resolve.

veeranj — Wed, 10 Sep 2014 13:42:21 GMT

Hi Albert,

hmm ya you are right ...

i am aware that that there is of no need to join as associative logic will build a relation between those two tables.
Since he has asked to join the tables,so i have added join keyword between those tables.

Thanks,

Anjee