Assigning 'Y' for blank & '- ' spaces in a field

[nevilledhamsiri]

Hi Members,

The field which carries 'Y' & 'N' to be left as they are while assigning 'y' for all blanks & '-' places.Also I have applied the where clause for records with 'y'. Once this is done, I need all the records which are not duplicated & with 'y' assigned to settled field to be loaded. Also see the error in the if formula written to achieve this which seems to be not working.

Thanks

Neville

[sunny_talwar · Accepted solution]

This should work

DATA:
LOAD [POLICY_ NUMBER],
	 AGENT, SCORE,
	 SETTLED,
	 IF(Len(Trim(SETTLED)) = 0 or SETTLED = '-', 'Y', SETTLED) AS NEW_SETTLED
FROM [DISTINCT LOAD.xlsx]
(ooxml, embedded labels, table is Sheet1);

[nevilledhamsiri]

Hi Sunny, Your solution is ok  as long as the restrictions that I have done through where clause is no longer there. But once it is applied, expected results wont come. I need the records which are assigned with 'Y' and not duplicated to be loaded. See the where clause used for achieve this.

LOAD [POLICY_ NUMBER], AGENT, SCORE, SETTLED,IF(LEN(TRIM(SETTLED))=0 OR SETTLED='-','Y',SETTLED)AS NEW_SETTLED
FROM [D:\DISTINCT LOAD.xlsx] (ooxml, embedded labels, table is Sheet1)WHERE NOT EXISTS([POLICY_ NUMBER])AND SETTLED='Y';

Any advice to achieve this.

Thanks

[nevilledhamsiri]

Once everything is done only following records to be the results.

RA001C125	P	200	Y
RA001C132	P	12	Y
RA001C133	L	12
RA001C134	P	12
RA001C135	O	12
RA001C136	P	12
RA001C137	L	12	-
RA001C138	K	12	-
RA001C139	L	12	-

[Brett_Bleess]

@sunny_talwar  Hey sir, this dang notification issue is killing me, just flagging things for you, sorry for the trouble on the notifications, hopefully the platform team can get that handled shortly for us.

Cheers,
Brett

[edwin · Accepted solution]

the first problem is your where statement.  with the SETTLED='Y' criteria, everything that does not have a Y is immediately excluded.  what i think you really wanted was to exclude 'N' - this way the spaces and dashes are pulled in and can be transformed properly.  try this:

...  WHERE NOT EXISTS([POLICY_ NUMBER])AND SETTLED<>'N';

[nevilledhamsiri]

Thanks Edvin

This is a solution  what I looked forward to

Neville