Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

Case 888 How to filter LAST_NAME=???

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;

1 Solution

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

8 Replies
Vegar
MVP
MVP

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
paulyeo11
Master
Master
Author

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
MVP
MVP

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
paulyeo11
Master
Master
Author

Hi Sir

Thank you for your sharing .

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

no name.png

Saravanan_Desingh

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;

View solution in original post

Saravanan_Desingh

commQV31.PNG

paulyeo11
Master
Master
Author

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