Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted

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?

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
Highlighted

What if  you add this in Calculated dimension?

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)
Highlighted
Contributor III
Contributor III

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

Highlighted

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

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)
Highlighted
Contributor III
Contributor III

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

Highlighted

Please check this Uploading a Sample

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)
Highlighted
Contributor III
Contributor III

i attached excel file test1 on the first thread

Highlighted

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

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)
Highlighted
Contributor III
Contributor III

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.

Highlighted

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?

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