How to make N/A if no attachment in the column field

[saivina2920]

I have the table data. In that, i have one column name called "Emp File(Attachment)".

Here i am listing some unnecessary link. For example, if i have some document it will be showing the document with entire path ==> //Server1/Files/Attach/EA10002.pdf

If document is not there then it will be showing as path ==> //Server1/Files/Attach/

What my client suggest is, if there no attachment in the like "//Server1/Files/Attach/" then it should show "N/A" else

it should list the document with the file ("//Server1/Files/Attach/EA10002.pdf")

How to change one particular column values as mentioned above.

below attached Qlikview report screenshot for your reference.

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD EmpNo, Name, [Emp File(Attachment)] As [Emp File(Original)],
	If(Index(SubField([Emp File(Attachment)],'/',-1),'.'),[Emp File(Attachment)],'N/A')
	 As [Emp File(Attachment)];
LOAD * INLINE [
    EmpNo, Name, Emp File(Attachment)
    EA10001, A, //Server1/Files/Attach/
    EA10002, B, //Server1/Files/Attach/EA10002.pdf
    EA10003, C, //Server1/Files/Attach/
    EA10004, D, //Server1/Files/Attach/EA10004.pdf
    EA10005, E, //Server1/Files/Attach/
];

[Saravanan_Desingh]

Output.

[saivina2920]

I am getting error "Incorrect syntax near the keyword 'If' while i am executing in SQL server.

OleDb error

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'If'.

SQL SELECT EmpNo, Name, [EmpFile(Attachment)] As [EmpFile(Original)],
EmpFile(Attachment) as EmpFileAttachmentOriginal,
If(Index(SubField([EmpFile(Attachment)],'/',-1),'.'),[EmpFile(Attachment)],'N/A') As [EmpFileAttachmentNew],
Department from Emp_table;

[saivina2920 · Accepted solution]

below is the correct query for ms sql server.

SELECT EmpNo, Name,Department,
[Emp File(Attachment)] as EmpFileAttachmentOriginal,
CASE WHEN LEN([Emp File(Attachment)])>LEN('//Server1/Files/Attach/') THEN [Emp File(Attachment)] ELSE 'N/A' END As [EmpFileAttachmentNew]
from Emp_table