Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi Bhavani,
Post the sample application here.
Thanks,
Venkata Sreekanth
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
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
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