Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

Case 943 How to Flag company name with Funny charater ?

Hi All

I have below raw data :-

Accounts_001:
LOAD * INLINE [
Company_ID
Advanced Remanufacturing and Technology Centre
Advanced Remanufacturing and Technology Centre (ARTC)
Agilent Technologies Singapore Pte Ltd
Agilent Technologies Singapore Pte Ltd
ANT
Applied Precision
Applied Precision Pte Ltd
ASA
Astar
Astars Technologies Pte Ltd
Automation & Control Solution Honeywell Pte Ltd
Automation & Control Technology Pte Ltd
Automation & Production System Pte Ltd
Avak System Pte Ltd
Avak Systems
Centroid Engineering Solutions Pte ltd
Centroid Engineering Solutions Pte Ltd
CMB
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (S) Pte Ltd
HP Singapore (S) Pte Ltd
HP Singapore Pte Ltd
ITE AMK college central
ite college
ITE College (Bedok)
ITE College (Bukit Batok)
ITE College (CCK)
ITE College (Central-AMK)
ITE College (Dover)
ITE College (MacPherson)
ITE College (Pasir Panjang)
ITE College (Simei)
ITE College (Tampines)
ITE College (Yishun)
ITE College Central
ITE College Central
ITE College East
ITE College West
ITE Electric Co Ltd
L & K Engineering Co., Ltd
L & N Engineering and Trading Pte Ltd
L&A Exceltec Pte Ltd
L&M Prestressing Pte Ltd
Motorola
Motorola Electronics (S) Pte Ltd
Motorola Electronics (S) Pte Ltd - Tuas
Motorola Electronics Pte Ltd
PSB Corporation Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
Rockwell Int'l Mfg Pte Ltd
T


]

 

I like to Flag when below situation :-

1) When company name only have only word for example : ASA , CMB , ANT , T

2) Flag Duplicated Company Name on the first word like :-

- Avak System Pte Ltd
- Avak Systems
3) Have funny charater like # @ ; :-

Rockwell Int'l Mfg Pte Ltd
L&A Exceltec Pte Ltd

 

Hope some one can advise me.

Paul Yeo

1 Solution

Accepted Solutions
Taoufiq_Zarra

@paulyeo11  like ?

Accounts_001:


LOAD *,

If(Findoneof(Company_ID,'!@#$%^&*_+'), 'Have funny charater',if(index(ltrim(Company_ID),' ')=0,'Only have only word'
))
as Flag,textbetween('__'&Company_ID,'__',' ') as midtmp
;
LOAD * INLINE [
Company_ID
Advanced Remanufacturing and Technology Centre
Advanced Remanufacturing and Technology Centre (ARTC)
Agilent Technologies Singapore Pte Ltd
Agilent Technologies Singapore Pte Ltd
ANT
Applied Precision
Applied Precision Pte Ltd
ASA
Astar
Astars Technologies Pte Ltd
Automation & Control Solution Honeywell Pte Ltd
Automation & Control Technology Pte Ltd
Automation & Production System Pte Ltd
Avak System Pte Ltd
Avak Systems
Centroid Engineering Solutions Pte ltd
Centroid Engineering Solutions Pte Ltd
CMB
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (S) Pte Ltd
HP Singapore (S) Pte Ltd
HP Singapore Pte Ltd
ITE AMK college central
ite college
ITE College (Bedok)
ITE College (Bukit Batok)
ITE College (CCK)
ITE College (Central-AMK)
ITE College (Dover)
ITE College (MacPherson)
ITE College (Pasir Panjang)
ITE College (Simei)
ITE College (Tampines)
ITE College (Yishun)
ITE College Central
ITE College Central
ITE College East
ITE College West
ITE Electric Co Ltd
L & K Engineering Co., Ltd
L & N Engineering and Trading Pte Ltd
L&A Exceltec Pte Ltd
L&M Prestressing Pte Ltd
Motorola
Motorola Electronics (S) Pte Ltd
Motorola Electronics (S) Pte Ltd - Tuas
Motorola Electronics Pte Ltd
PSB Corporation Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
Rockwell Int'l Mfg Pte Ltd
T
];
left join 
load count(midtmp) as countTmp,midtmp resident Accounts_001 group by midtmp;

Final:

load Company_ID,if(countTmp>1,'Duplicated Company Name',Flag) as Flag resident Accounts_001;

drop table Accounts_001;

 

output:

Capture.PNGCapture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

10 Replies
Kushal_Chawda

@paulyeo11  What are the duplicates here?

Anil_Babu_Samineni

@paulyeo11  Assuming like below

If(Match(Company_ID, 'ASA','CMB','ANT','T'), 'When company name only have only word',
If(Index(Subfield(Company_ID,' ',1), 'Avak'), If(Index(Subfield(Company_ID,' ',1), 'Avak1'), 'Flag Duplicated Company Name on the first word',
If(Findoneof(Company_ID,'!@#$%^&*()_+'), 'Have funny charater')))) as Flag

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vijayaganesh_s
Partner - Contributor III
Partner - Contributor III

Hi,

To find a single worded string you can try the code below... also the distinct load removes some repeated records which are duplicated but by seeing  your requirement, for humans it is easy to identify duplicated companies whereas for Qlik I'm not sure how it will handle your requirement 🤔.

LOAD DISTINCT
Company_ID,
(SubStringCount(Company_ID,' ')=0) * -1 AS _Flag
;

All the best!

BR,

Vijay

paulyeo11
Master
Master
Author

Hi Anil

Thank you for sharing .

I try below , it does not work.

Accounts_001:
LOAD *,

If(Match(Company_ID, 'ASA','CMB','ANT','T'), 'When company name only have only word',
If(Index(Subfield(Company_ID,' ',1), 'Avak'), If(Index(Subfield(Company_ID,' ',1), 'Avak1'), 'Flag Duplicated Company Name on the first word',
If(Findoneof(Company_ID,'!@#$%^&*()_+'), 'Have funny charater')))) as Flag

INLINE [
Company_ID
Advanced Remanufacturing and Technology Centre
Advanced Remanufacturing and Technology Centre (ARTC)
Agilent Technologies Singapore Pte Ltd
Agilent Technologies Singapore Pte Ltd
ANT
Applied Precision
Applied Precision Pte Ltd
ASA
Astar
Astars Technologies Pte Ltd
Automation & Control Solution Honeywell Pte Ltd
Automation & Control Technology Pte Ltd
Automation & Production System Pte Ltd
Avak System Pte Ltd
Avak Systems
Centroid Engineering Solutions Pte ltd
Centroid Engineering Solutions Pte Ltd
CMB
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (S) Pte Ltd
HP Singapore (S) Pte Ltd
HP Singapore Pte Ltd
ITE AMK college central
ite college
ITE College (Bedok)
ITE College (Bukit Batok)
ITE College (CCK)
ITE College (Central-AMK)
ITE College (Dover)
ITE College (MacPherson)
ITE College (Pasir Panjang)
ITE College (Simei)
ITE College (Tampines)
ITE College (Yishun)
ITE College Central
ITE College Central
ITE College East
ITE College West
ITE Electric Co Ltd
L & K Engineering Co., Ltd
L & N Engineering and Trading Pte Ltd
L&A Exceltec Pte Ltd
L&M Prestressing Pte Ltd
Motorola
Motorola Electronics (S) Pte Ltd
Motorola Electronics (S) Pte Ltd - Tuas
Motorola Electronics Pte Ltd
PSB Corporation Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
Rockwell Int'l Mfg Pte Ltd
T


]

 

paulyeo11
Master
Master
Author

Hi Sir

The Duplicate I mean for company name :-

Apple Pte Let
Apple P/L
Apple (S) P/L

If I can filter more then one Apple , I know there is duplicate for above company.

As this is my company database , due to human data entry error .
paulyeo11
Master
Master
Author

Hi Vijayaganesh

Thank you for your sharing.

Part (1) Your expression work fine on help me filter those company with one word :-

Accounts_001:
LOAD *,

If(Findoneof(Company_ID,'!@#$%^&*_+'), 'Have funny charater',
)
as Flag

INLINE [
Company_ID
Automation & Control Solution Honeywell Pte Ltd
Avak Systems
L & K Engineering Co., Ltd
L & N Engineering and Trading Pte Ltd
L&A Exceltec Pte Ltd
L&M Prestressing Pte Ltd
Motorola
Rockwell Int'l Mfg Pte Ltd
Agilent Technologies Singapore Pte Ltd
ANT
ASA
Astar
T

]

 

Part (2) I also have Anil script work fine on detect funny character , which working fine too :-

Accounts_001:
LOAD *,

If(Findoneof(Company_ID,'!@#$%^&*_+'), 'Have funny charater',
)
as Flag

INLINE [
Company_ID
Automation & Control Solution Honeywell Pte Ltd
Avak Systems
L & K Engineering Co., Ltd
L & N Engineering and Trading Pte Ltd
L&A Exceltec Pte Ltd
L&M Prestressing Pte Ltd
Motorola
Rockwell Int'l Mfg Pte Ltd
Agilent Technologies Singapore Pte Ltd
ANT
ASA
Astar
T

]

Now my issue is how to combine both of your script into one.

Hope you can help me.

Paul Yeo

 

Kushal_Chawda

@paulyeo11  below is also duplicate?

ite college
ITE College (Bedok)
ITE College (Bukit Batok)
ITE College (CCK)

paulyeo11
Master
Master
Author

Hi sir

Yes , these are consider as duplicate.
Taoufiq_Zarra

@paulyeo11  like ?

Accounts_001:


LOAD *,

If(Findoneof(Company_ID,'!@#$%^&*_+'), 'Have funny charater',if(index(ltrim(Company_ID),' ')=0,'Only have only word'
))
as Flag,textbetween('__'&Company_ID,'__',' ') as midtmp
;
LOAD * INLINE [
Company_ID
Advanced Remanufacturing and Technology Centre
Advanced Remanufacturing and Technology Centre (ARTC)
Agilent Technologies Singapore Pte Ltd
Agilent Technologies Singapore Pte Ltd
ANT
Applied Precision
Applied Precision Pte Ltd
ASA
Astar
Astars Technologies Pte Ltd
Automation & Control Solution Honeywell Pte Ltd
Automation & Control Technology Pte Ltd
Automation & Production System Pte Ltd
Avak System Pte Ltd
Avak Systems
Centroid Engineering Solutions Pte ltd
Centroid Engineering Solutions Pte Ltd
CMB
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (Pte) Ltd
HP Singapore (S) Pte Ltd
HP Singapore (S) Pte Ltd
HP Singapore Pte Ltd
ITE AMK college central
ite college
ITE College (Bedok)
ITE College (Bukit Batok)
ITE College (CCK)
ITE College (Central-AMK)
ITE College (Dover)
ITE College (MacPherson)
ITE College (Pasir Panjang)
ITE College (Simei)
ITE College (Tampines)
ITE College (Yishun)
ITE College Central
ITE College Central
ITE College East
ITE College West
ITE Electric Co Ltd
L & K Engineering Co., Ltd
L & N Engineering and Trading Pte Ltd
L&A Exceltec Pte Ltd
L&M Prestressing Pte Ltd
Motorola
Motorola Electronics (S) Pte Ltd
Motorola Electronics (S) Pte Ltd - Tuas
Motorola Electronics Pte Ltd
PSB Corporation Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
PSB Technologies Pte Ltd
Rockwell Int'l Mfg Pte Ltd
T
];
left join 
load count(midtmp) as countTmp,midtmp resident Accounts_001 group by midtmp;

Final:

load Company_ID,if(countTmp>1,'Duplicated Company Name',Flag) as Flag resident Accounts_001;

drop table Accounts_001;

 

output:

Capture.PNGCapture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉