topic Re: Case 888 How to filter LAST_NAME=??? in QlikView

Case 888 How to filter LAST_NAME=???

paulyeo11 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

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

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;

Re: Case 888 How to filter LAST_NAME=???

Vegar — Sat, 13 Jun 2020 05:26:38 GMT

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

Re: Case 888 How to filter LAST_NAME=???

paulyeo11 — Sat, 13 Jun 2020 05:31:55 GMT

Hi Sir

I have try below :-

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

Re: Case 888 How to filter LAST_NAME=???

Vegar — Sat, 13 Jun 2020 07:31:52 GMT

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.

Re: Case 888 How to filter LAST_NAME=???

paulyeo11 — Sat, 13 Jun 2020 07:51:24 GMT

Hi Sir

Thank you for your sharing .

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

Re: Case 888 How to filter LAST_NAME=???

Saravanan_Desingh — Sat, 13 Jun 2020 20:44:04 GMT

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;

Re: Case 888 How to filter LAST_NAME=???

Saravanan_Desingh — Sat, 13 Jun 2020 20:44:59 GMT

Re: Case 888 How to filter LAST_NAME=???

paulyeo11 — Sun, 14 Jun 2020 03:27:53 GMT

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

Re: Case 888 How to filter LAST_NAME=???

Saravanan_Desingh — Sun, 14 Jun 2020 04:00:40 GMT

you welcome