Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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.

1 Solution

Accepted Solutions
Anil_Babu_Samineni

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

9 Replies
Anil_Babu_Samineni

What if  you add this in Calculated dimension?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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.

CalculatedField1.jpgIfAndCondition.jpg

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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)

TEST.jpg

Anil_Babu_Samineni

Please check this Uploading a Sample

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

i attached excel file test1 on the first thread

Anil_Babu_Samineni

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

Capture.PNG

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

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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.

Anil_Babu_Samineni

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)