topic Re: if Logic Help in QlikView

if Logic Help

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: if Logic Help

Anonymous — Tue, 13 Mar 2018 07:06:49 GMT

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

Re: if Logic Help

Anonymous — Tue, 13 Mar 2018 11:27:41 GMT

Hi Bhavani,

Post the sample application here.

Thanks,

Venkata Sreekanth

Re: if Logic Help

Anonymous — Tue, 13 Mar 2018 11:30:45 GMT

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

Re: if Logic Help

anagharao — Tue, 13 Mar 2018 11:43:55 GMT

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

Re: if Logic Help

Anonymous — Tue, 13 Mar 2018 12:09:23 GMT

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