Join Fucntion Question

[mario-sarkis]

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

[Gysbert_Wassenaar]

LOAD

     ID,

     User,

     'allds' as Flag2

FROM .... ;

left join

LOAD

     ID2 as ID,

     ID2,

     USER as User,

     'allocated' as Flag1

FROM .... ;

[krishna20]

Hi,

Are you looking for this??

[prma7799 · Accepted solution]

PFA

[#5]

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;