if Logic Help

[bhavvibudagam]

Hi Experts,

Can any one please help me on below requirement.

I have 4 fields like DateValue,StringValue,TextValue and NumberValue. From these 4 Fields I have to derive Value field based on below condition.

if DateValue,StringValue,TextValue is Null then NumberValue need to store under Value Field.

if DateValue,TextValue,NumberValue  is Null then StringValue need to store under Value Field

if StringValue ,TextValue,NumberValue  is Null then DateValue need to store under Value Field

if DateValue ,StringValue ,NumberValue  is Null then TextValue need to store under Value Field

Please find the below sample data.

DateValue                          StringValue           TextValue                                 NumberValue       Value(Output)

-                                          -                              -                                                1,000000             1,000000  

-                                          aafon6                    -                                                 -                            aafon6 

13/02/2018  10:51:20         -                              -                                                 -                            13/02/2018  10:51:20

-                                         -                             This is Infrastructure Change      -                            This is Infrastructure Change

Thanks in advance.

[Anonymous]

Hi,

You can try:

if(isnull(DateValue) and isnull(StringValue) and isnull(TextValue ), NumberValue,

     if(isnull(DateValue) and isnull(TextValue) and isnull(NumberValue), StringValue,

          if(isnull(StringValue) and isnull(TextValue) and isnull(NumberValue), DateValue,

                if(isnull(DateValue) and isnull(StringValue) and isnull(NumberValue), TextValue

                    )

               )

          )

     ) as Value

[Anonymous]

Hi Bhavani,

Post the sample application here.

Thanks,

Venkata Sreekanth

[Anonymous]

Hi,

If I may ask, what data types are those fields?

Alternatively, instead of using the "and" operator in the if statement, try using an "or" operator. So something like:

if(isnull(DateValue) or isnull(StringValue) or isnull(TextValue ), NumberValue,

     if(isnull(DateValue) or isnull(TextValue) or isnull(NumberValue), StringValue,

          if(isnull(StringValue) or isnull(TextValue) or isnull(NumberValue), DateValue,

                if(isnull(DateValue) or isnull(StringValue) or isnull(NumberValue), TextValue

                    )

               )

          )

     ) as Value

Or you could work around the above

[anagharao · Accepted solution]

try

IF(LEN(CustomValue)>0, CustomValue

      ,IF(LEN(ChampionName)>0, ChampionName

      ,IF(LEN([String Value])>0, [String Value]

      ,IF(LEN([Date Value])>0, [Date Value]

      ,IF(LEN([Text Value])>0, [Text Value],''))))) AS RESULT

[Anonymous]

Hi,

Use thee below code to solve your problem. For your reference , application is added in attachments.

IF(LEN([Champion Name])>0, [Champion Name],

IF(LEN([Custom Value])>0, [Custom Value],

    IF(LEN(DateValue)>0, DateValue,

    IF(LEN(NumberValue)>0, NumberValue,

    IF(LEN(StringValue)>0, StringValue,

    IF(LEN(TextValue)>0, TextValue))))))

Thanks,

Venkata Sreekanth