topic Re: Might be missing function command in Logical function-Creating calculated field? in QlikView

Might be missing function command in Logical function-Creating calculated field?

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

I about to create a calculated fields associated with some logical expression using "and","or" to define the calculation within specified range of accounts. When i use the syntax in the expression using 'and' function is not recognizing as predefined function where as the same expression works well with Data load editor script.

IF([ACCOUNTNUM]>=500000 and [ACCOUNTNUM]<=599999,'NET INCOME BEFORE ZAKAT','OTHERS')..

if I do in data load editor i'm facing error in Data synchronization. To avoid data synchronization, i need to use table edit and do calculated field.

Is there is any way to handle this issue.

Re: Might be missing function command in Logical function-Creating calculated field?

Anil_Babu_Samineni — Sun, 21 Jan 2018 10:51:16 GMT

What if you add this in Calculated dimension?

Re: Might be missing function command in Logical function-Creating calculated field?

Anonymous — Sun, 21 Jan 2018 11:42:25 GMT

not showing the preview and showing error as attached, for your kind information on the Fx (function tab, i found different function command but not the 'and', 'or' to do the calculation. attached screenshot for your verification.

and not showing any result in preview result.

Re: Might be missing function command in Logical function-Creating calculated field?

Anil_Babu_Samineni — Sun, 21 Jan 2018 11:50:34 GMT

I've tested that. It's working as expected. Will you able to provide sample to test

Re: Might be missing function command in Logical function-Creating calculated field?

Anonymous — Sun, 21 Jan 2018 12:12:28 GMT

I don't find a way to attach excel file. but this is the sample and range i need to calculate same as description as range (manual)

Re: Might be missing function command in Logical function-Creating calculated field?

Anil_Babu_Samineni — Sun, 21 Jan 2018 12:15:31 GMT

Please check this Uploading a Sample

Re: Might be missing function command in Logical function-Creating calculated field?

Anonymous — Sun, 21 Jan 2018 12:53:14 GMT

i attached excel file test1 on the first thread

Re: Might be missing function command in Logical function-Creating calculated field?

Anil_Babu_Samineni — Sun, 21 Jan 2018 13:19:38 GMT

What i understand here is you are not support to use operations or functions which are not cover from section provided by Sense.

So, Instead create manual using If..Else condition in script manually. Or else, My suggestion is this?

Re: Might be missing function command in Logical function-Creating calculated field?

Anonymous — Sun, 21 Jan 2018 13:57:16 GMT

Some extend its work good, but we can't put a conditional expression like filter in particular range.

for example for expenses account 60000 series we have department dimension, so we need to range the expression as bucket 60000 to 699999 which hold the value for department dimension which is filed dimension1 (dept)='10' for sales and 20 for Administration.

similar as below.

IF([ACCOUNTNUM]>=600000 and [ACCOUNTNUM]<=660200 AND [ACCOUNTNUM]<>623300 and [DIMENSION]>=06 and [DIMENSION]<=06,'SALES & DISTRIBUTION EXPENSE',

but this straight forward in load script. i feel that this type of filter is missing in bucket or may i'm not aware of this function how to use.

Sorry to bother you.

Re: Might be missing function command in Logical function-Creating calculated field?

Anil_Babu_Samineni — Mon, 22 Jan 2018 00:21:35 GMT

There is no suck functionality Bucket with two conditions not available while loading. So, you should use manually within script itself using?

IF(([ACCOUNTNUM]>=600000 and [ACCOUNTNUM]<=660200) AND ([ACCOUNTNUM]<>623300) and ([DIMENSION]>=06 and [DIMENSION]<=06),'SALES & DISTRIBUTION EXPENSE',

Note - What is the usage of Blue part here? You just call DIMENSION = 06 instead >= and <= with same value? Does it make sense?