topic Join Fucntion Question in QlikView

Join Fucntion Question

mario-sarkis — Tue, 29 Sep 2015 07:01:02 GMT

Hey All

how can solve this in the script ? here is a sample of data that i have

Table A
ID	User
1	MA
2	MA
3	SA
4	SA
5	SE
6	SE

Table B
ID2	USER
2	MA
3	SA
5	SE

RESULT That i need:

RESULT
ID	ID2	User
1		MA
2	2	MA
3	3	SA
4		SA
5	5	SE
6		SE

i need to compare the IDs if equals need to create a field that said allocated and null if not , and another field called 'AllIds'

FInal output:

ID	ID	User	Flag1	Flag2
1		MA		allIds
2	2	MA	allocated	allIds
3	3	SA	allocated	allIds
4		SA		allIds
5	5	SE	allocated	allIds
6		SE		allIds

thank you hope you can help

Re: Join Fucntion Question

Gysbert_Wassenaar — Tue, 29 Sep 2015 09:38:16 GMT

LOAD

ID,

User,

'allds' as Flag2

FROM .... ;

left join

LOAD

ID2 as ID,

ID2,

USER as User,

'allocated' as Flag1

FROM .... ;

Re: Join Fucntion Question

krishna20 — Tue, 29 Sep 2015 09:50:05 GMT

Hi,

Are you looking for this??

Re: Join Fucntion Question

prma7799 — Tue, 29 Sep 2015 09:51:03 GMT

PFA

Re: Join Fucntion Question

Tue, 29 Sep 2015 10:25:33 GMT

Try this -

TABLEA:

LOAD *, 'allids' AS FLAG2;

LOAD * INLINE [

ID, User

1, MA

2, MA

3, SA

4, SA

5, SE

6, SE

];

TABLEB:

LOAD * INLINE [

ID2, USER

2, MA

3, SA

5, SE

];

LEFT JOIN(TABLEA)

LOAD ID2 AS ID,ID2, 'allocated' AS FLAG1 RESIDENT TABLEB;

DROP TABLE TABLEB;