Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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
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
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.
Hi Sir
Thank you for your sharing .
now error disappear , but it does not able to Flag Last_Name = ??? .
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;
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
you welcome