Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
sjhussain
Partner
Partner

Read formula from Excel file and apply in script

I have an excel sheet in which i have defined formulas for each of the CODE as follows:

CODE, THRESHOLD

AJS,AGE>15

AJSS, AGE<3

AGE is a column name in the same table

How can i use the THRESHOLD as a formula and then use it to define a dimension.  Something as below

if (applymap('MAP_THRESHOLD',CODE,null()),1,0) 

The applymap will return the formula and then i can apply it in the if condition.  Is there a way to use it?

Labels (2)
4 Replies
HirisH_V7
Master
Master

Check this,

I am not still clear, what your'e trying to achieve.

MAP_THRESHOLD:
Mapping LOAD * INLINE [
    THRESHOLD, CODE
    AGE>15, AJS
    AGE<3, AJSS
];

Age:
Load *,
If (Len(applymap('MAP_THRESHOLD',Age_Bucket))>=1,1,0) as Dimension,
applymap('MAP_THRESHOLD',Age_Bucket,'#NA') as CODE;
LOAD *,
If(Age>15,'AGE>15',
IF( Age<3, 'AGE<3')) as Age_Bucket
INLINE [
    Age
    1
    2
    66
    16
    7
];

 

 

HirisH
“Aspire to Inspire before we Expire!”
sjhussain
Partner
Partner
Author

hirishv7,

Thanks for the response.

Actually what I want is to have an kind of formula as a text in the field (AGE was just an example)

CODE, FORMULA

AJS, SALES>100

AJSS, SALES <50

I want to read the formula and then apply it as a condition.  The applymap will give me the formula but how can i apply the formula as a condition.

applymap('MAP_THRESHOLD',CODE,null()) will give me the FORMULA

if(applymap('MAP_THRESHOLD',CODE,nul()),1,0) should give me the following

if(SALES>100,1,0)

but the challenge it is that it is returning text and i cannot use SALES>100 as formula.

Hope this clears.

Thanks.

HirisH_V7
Master
Master

Check this, using evaluate function.

Spoiler

 

 

Map:
Mapping LOAD * INLINE [
CODE, FORMULA
AJS, SALES>100
AJSS, SALES<50
];


SET vEval = Evaluate($1&$2);

Data:
LOAD *,
If($(vEval(SALES,Evaluator)),1,0) AS Validate;
LOAD *,
Mid(ApplyMap('Map',CODE),6) as Evaluator
INLINE [
CODE,SALES
AJS, 55
AJA, 89
AJS, 110
AJSS, 40
AJSS, 90
];

 

HTH,

PFA for ref.

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Brett_Bleess
Support (Former)
Support (Former)

Did Hirish's last comment and example app solve your issue?  If it did, be sure to give him credit by clicking the Accept as Solution button on the post.  If you are still stuck or you did something else to solve things, it would be great if you can post that and mark that as the solution...

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.