Left Join Query

[Karl_Hart]

I have a list of 13,000 properties of which I want to be able to identify 5 separately. I want to create an extra field that has a Y/N flag on it.

My first attempt was to try and do a applymap, but I couldn't get tat to work so I tried a left join.

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

PropertyTemp:

LOAD * INLINE [

Prop_Num, Exclude_Flag

1234, Y

2345, Y

3456, Y

4567, Y

];

LOAD Prop_Num

FROM FILEPATH.qvd

(qvd);

Left Join load * resident PropertyTemp;

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

This worked in applying a Y flag to the above properties but how do I get it to apply a 'N' to all the remaining properties in my list?

Is there a better way of doing it?

Thank

[ali_hijazi]

after the left join write the following

tempProp_Num:

noConcatenate

load Prop_Num,if(not isnull(Exclude_Flag),Exclude_Flag,'N') as Exclude_Flag

resident Prop_Num;

drop table Prop_Num;

rename table tempProp_Num to Prop_Num;

hope this helps

[ashfaq_haseeb]

Hi try something like this in qvd itself.

PropertyTemp:

Load *,if(match(Prop_Num,'1234','2345','3456','4567'),'Y','N') as Flag;

LOAD * INLINE [

Prop_Num

1234

2345

3456

4567

12

13

];

regards

ASHFAQ

[er_mohit · Accepted solution]

hi try to use exist function like

PropertyTemp:

LOAD * INLINE [

Prop_Num, Exclude_Flag

1234, Y

2345, Y

3456, Y

4567, Y

];

LOAD Prop_Num,if(exists(Prop_Num),'Y','N') as Exclude_Flag

FROM FILEPATH.qvd

(qvd);