Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below is the logic I am using.
It is flagging the two cells that are white under MOP2.
I was hoping that on line two after the INV','_', would work but no luck
**** (Match(MOP2,'FACI','PRIV','GUAR','CASH','CHRG','INV',' ',
What Can I use to exclude the blank cells?
if((Match(MOP,'FACI','PRIV','GUAR','CASH','CHRG','INV')<1 and ElecAdj = 0 ) or
(Match(MOP2,'FACI','PRIV','GUAR','CASH','CHRG','INV',' ',)<1 and ElecAdj2 = 0),1,0) as Flag,
Hi,
using the len and the trim function you may achieve what you need.
Example (new code in bold):
if((Match(MOP,'FACI','PRIV','GUAR','CASH','CHRG','INV')<1 and ElecAdj = 0 ) or
(Match(MOP2,'FACI','PRIV','GUAR','CASH','CHRG','INV',' ',)<1 and len(trim(MOP2)) >0 and ElecAdj2 = 0),1,0) as Flag,
What they do:
Trim: Remove white spaces from beginning and end of a specific value
Len: Return the length in characters of a specific value
Combining these two functions we avoid nulls and blanks, as both return zero in its length when trimmed.
Please let me know if this helps.
Kind regards,
Is it really blank! Try len(MOP2)
some are blank and some have a something in it but can see it Maybe a space?
Hi,
using the len and the trim function you may achieve what you need.
Example (new code in bold):
if((Match(MOP,'FACI','PRIV','GUAR','CASH','CHRG','INV')<1 and ElecAdj = 0 ) or
(Match(MOP2,'FACI','PRIV','GUAR','CASH','CHRG','INV',' ',)<1 and len(trim(MOP2)) >0 and ElecAdj2 = 0),1,0) as Flag,
What they do:
Trim: Remove white spaces from beginning and end of a specific value
Len: Return the length in characters of a specific value
Combining these two functions we avoid nulls and blanks, as both return zero in its length when trimmed.
Please let me know if this helps.
Kind regards,
I would purge the garbage characters during load using purgechar ()
PurgeChar and KeepChar Functions
Preferably also replace null values with "n/a" or some text
Great thank you, Now I have a new problem.
Its counting the top two and it should not.
If there is an MOP2 it can be MOP can have a 1 or 0 in ElecAdj. and then if there is a zero in ElecAdj2 has a zero count it if a 1 do not count it/