Case 943 How to Flag company name with Funny charater ?

[paulyeo11]

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

[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

[vijayaganesh_s]

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]

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]

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]

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]

Hi sir

Yes , these are consider as duplicate.

[Taoufiq_Zarra · Accepted solution]

@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: