Case 888 How to filter LAST_NAME=???

[paulyeo11]

Hi All

I have below load script , like to know why filter last name cannot work :-

tab1:
LOAD * INLINE [
LAST_NAME,Email
???,
???,
???,0rts@stee.stengg1.com
???,0rts@stee.stengg.com
zcrm_4325140000000394899,a.hong.my@sg.azbil.com
zcrm_4325140000000394902,a.koh.hk@sg.azbil.com
zcrm_4325140000000405882,catherine@mattenplant.com.sg
zcrm_4325140000000405884,catherine@mattenplant.com.sg
zcrm_4325140000000405885,
zcrm_4325140000000405886,

];

Left Join(tab1)
LOAD Email,
If(Count(Email)>1, 'Y', 'N') As Flag_Duplicate,
If(Email='', 'N', 'Y') As F_NO_EMAIL
//If(LAST_NAME='???', 'N', 'Y') As F_NO_LAST_NAME
Resident tab1
Group By Email;

[Vegar]

It looks fine. If you are experiencing trouble then it could be (it shouldn't as you are using single quotes) cased by the single char wildchar capabilities to the question mark (?)

Try to see if match gives a better result :

If(match(LAST_NAME, '???'), 'N', 'Y') As F_NO_LAST_NAME

[paulyeo11]

Hi Sir

I have try below :-

tab1:
LOAD * INLINE [
LAST_NAME,Email
???,
???,
???,0rts@stee.stengg1.com
???,0rts@stee.stengg.com
zcrm_4325140000000394899,a.hong.my@sg.azbil.com
zcrm_4325140000000394902,a.koh.hk@sg.azbil.com
zcrm_4325140000000405882,catherine@mattenplant.com.sg
zcrm_4325140000000405884,catherine@mattenplant.com.sg
zcrm_4325140000000405885,
zcrm_4325140000000405886,

];

Left Join(tab1)
LOAD Email,
If(Count(Email)>1, 'Y', 'N') As Flag_Duplicate,
If(match(LAST_NAME, '???'), 'N', 'Y') As F_NO_LAST_NAME_,
If(Email='', 'N', 'Y') As F_NO_EMAIL
Resident tab1
Group By Email;

I Get error msg :-

Invalid expression
Left Join(tab1)
LOAD Email,
If(Count(Email)>1, 'Y', 'N') As Flag_Duplicate,
If(match(LAST_NAME, '???'), 'N', 'Y') As F_NO_LAST_NAME_,
If(Email='', 'N', 'Y') As F_NO_EMAIL
Resident tab1
Group By Email

[Vegar]

The error is caused by this load:

LOAD Email,
If(Count(Email)>1, 'Y', 'N') As Flag_Duplicate,
If(match(LAST_NAME, '???'), 'N', 'Y') As F_NO_LAST_NAME_,
If(Email='', 'N', 'Y') As F_NO_EMAIL
Resident tab1
Group By Email;

You can't use group by Email as you are not usin aggregate functions for defining F_NO_LAST_NAME_ and  F_NO_EMAIL

Maybe this will help?

LOAD Email,
If(Count(Email)>1, 'Y', 'N') As Flag_Duplicate,
If(match(LAST_NAME, '???'), 'N', 'Y') As F_NO_LAST_NAME_,
If(Email='', 'N', 'Y') As F_NO_EMAIL
Resident tab1
Group By
   Email,
   If(Email='', 'N', 'Y'),
   If(match(LAST_NAME, '???'), 'N', 'Y')

;

Or you could do the group by in a later step after you created the two Flag fields.

[paulyeo11]

Hi Sir

Thank you for your sharing .

now  error disappear , but it does not able to Flag Last_Name = ???  .

[Saravanan_Desingh · Accepted solution]

Check this.

tab1:
LOAD * INLINE [
LAST_NAME,Email
???,
???,
???,0rts@stee.stengg1.com
???,0rts@stee.stengg.com
zcrm_4325140000000394899,a.hong.my@sg.azbil.com
zcrm_4325140000000394902,a.koh.hk@sg.azbil.com
zcrm_4325140000000405882,catherine@mattenplant.com.sg
zcrm_4325140000000405884,catherine@mattenplant.com.sg
zcrm_4325140000000405885,
zcrm_4325140000000405886,
];

Left Join(tab1)
LOAD LAST_NAME,
	If(Count(Email)>1, 'Y', 'N') As F_DUP_EMAIL,
	If(Concat(Email)='', 'N', 'Y') As F_NO_EMAIL,
	If(Concat(DISTINCT LAST_NAME)='???', 'N', 'Y') As F_NO_LAST_NAME
Resident tab1
Group By LAST_NAME;

[Saravanan_Desingh]

[paulyeo11]

Hi Sir

Thank you very much for giving the solution that is work . When i try to implement into my actual doc , i can feel my head very stress. need to take a rest. Any way it is good problem.

Paul

[Saravanan_Desingh]

you welcome