Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

left join one record

hi,

i have this

Table1

ID     

1

2

3

4

Table2

ID      A

1        x

1        y

2        x

3        z

i need to left join a table1 with table2 but i only one description

how can i do this?

Tags (1)
1 Solution

Accepted Solutions

Re: left join one record

Not able to understand your requirements !

Can you provide us the final result you are looking for?

Thanks

====

use below script

T1:

Load * Inline

[

  ID

  1

  2

  3

  4

];

T2:

Load *, AutoNumber(ID & A) as Sequence Inline

[

  ID, A

  1, x

  1, y

  2, x

  3, z

];

Left Join (T2) Load ID, Min(Sequence) as MinKey Resident T2 Group By ID;

Inner Join (T1)

Load * Resident T2 Where Sequence = MinKey;

Drop Table T2;

Drop Fields Sequence, MinKey;

6 Replies

Re: left join one record

Not able to understand your requirements !

Can you provide us the final result you are looking for?

Thanks

====

use below script

T1:

Load * Inline

[

  ID

  1

  2

  3

  4

];

T2:

Load *, AutoNumber(ID & A) as Sequence Inline

[

  ID, A

  1, x

  1, y

  2, x

  3, z

];

Left Join (T2) Load ID, Min(Sequence) as MinKey Resident T2 Group By ID;

Inner Join (T1)

Load * Resident T2 Where Sequence = MinKey;

Drop Table T2;

Drop Fields Sequence, MinKey;

Highlighted
neetha_p
Honored Contributor

Re: left join one record

Hi,

Please Try:

Table1:
load * inline
[
ID

1

2

3

4
]
;


Table2:
Left Join(Table1)
load * inline
[

ID,A

1,x

1,y

2,x

3,z
]
;

Not applicable

Re: left join one record

result Table

ID   A

1     x

2     x

3     z

Re: left join one record

check my previous reply...

Not applicable

Re: left join one record

Actually I get your result by just loading the second bulk of items by using not exists (ID)

LOAD * INLINE  [

ID, A,

1,X

1,Y

2,X

3,Z]

Where not Exists(ID) ;

Not applicable

Re: left join one record

But in the case You still need to do a left Join, here is the order to achieve the same result.

Table1:

LOAD * INLINE  [

ID, A,

1,X

1,Y

2,X

3,Z]

Where not Exists(ID) ;

left join

LOAD * INLINE [

ID,

1

2

3

4 ];