topic Left Join Query in QlikView

Left Join Query

Karl_Hart — Thu, 10 Jul 2014 11:42:13 GMT

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

Re: Left Join Query

ali_hijazi — Thu, 10 Jul 2014 11:45:16 GMT

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

Re: Left Join Query

ashfaq_haseeb — Thu, 10 Jul 2014 11:47:44 GMT

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

];

regards

ASHFAQ

Re: Left Join Query

er_mohit — Thu, 10 Jul 2014 11:49:55 GMT

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);