IF(NOT ISNULL(ROUND) AND NOT ISNULL(AMENDMENT),SUBFIELD(RFQ_NO,'-',1),

[Kohli]

IF(NOT ISNULL(ROUND) AND NOT ISNULL(AMENDMENT),SUBFIELD(RFQ_NO,'-',1),

    IF(NOT ISNULL(ROUND) AND ISNULL(AMENDMENT),SUBFIELD(RFQ_NO,'-',1),

    IF(ISNULL(ROUND) AND NOT ISNULL(AMENDMENT),SUBFIELD(RFQ_NO,',',1),

    RFQ_NO))) AS SPW    for this what answer I will get?

[jonathandienst]

>> for this what answer I will get?

Hard to say without any knowledge of the structure/value of RFG_NO, ROUND and AMENDMENT, but clearly it is adjusting the value according whether either or both of the latter are null.

Why don't you break this down into parts and work it out step by step?

[rahulpawarb]

Suppose RFQ_NO has value "ABC-1000-2000,007,008"

1. If ROUND field has some value and AMENDMENT field has some value then result of expression will be ABC

2. If ROUND field has some value and AMENDMENT field is null then result of expression will be ABC

3. If ROUND field is null and AMENDMENT field has some value then result of expression will be ABC-1000-2000

4. Else expression will return RFQ_NO value

As per Jonathan's inputs I have fine tuned the expression as below:

IF(ISNULL(ROUND) AND NOT ISNULL(AMENDMENT), SUBFIELD(RFQ_NO,',',1),
   IF(NOT ISNULL(ROUND), SUBFIELD(RFQ_NO,'-',1),
   RFQ_NO)) AS SPW

Hope this will be helpful.

Regards!

Rahul Pawar