Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

IF Statement Calculation

I have the following If statement in the load script but it is an invalid expression. Does anyone have any thoughts on how i can make this work?

IF((Match(Type,'FTE') and sum(Number)) > (Match(Type,'ACTUAL') and sum(Number)),'OVERSTAFFED','UNDERSTAFFED')

6 Replies
Highlighted
Partner
Partner

Re: IF Statement Calculation

You should set a flag for Type = FTE and using the Flag you can create a expression in UI.

Else you can use below the expression in UI

if(sum({<Type={"FTE"}>}Number)>sum({<Type={"ACTUAL"}>}Number),'OVERSTAFFED','UNDERSTAFFED')

Are you using the expression in Load statement or assigning the value to a variable?

Highlighted
Not applicable

Re: IF Statement Calculation

I am using it in the load statement, I know i can do it in the UI but i need it done in the load script.

Highlighted
Partner
Partner

Re: IF Statement Calculation

Hi Zeth

Please, share your associative model data

Highlighted
Not applicable

Re: IF Statement Calculation

This is a flat data table is one of my constraints.

Highlighted
Honored Contributor III

Re: IF Statement Calculation

It won't work so easily in script with the reason of having aggregation involved, you may try as below -

1. First calculate sum(number) using where clause and group by clause in separate table

2. join the sum no to the old table so that sum field is available in all records

3. then compare the type field with the new sum field as you are trying to do.

May not completely correct, if you share your script, can add something further.

Regards,

Highlighted
Valued Contributor II

Re: IF Statement Calculation

Zeth,

It's hard to say without more info.  Making some assumptions you could do something like this:

Data:

LOAD * INLINE [

    Team, Type, Number

    A, Actual, 4

    A, Actual, 3

    A, FTE, 6

    B, Actual, 1

    B, Actual, 2

    B, FTE, 4

];

Summary:

load *,

if(Actual>FTE,'OVERSTAFFED','UNDERSTAFFED') as Status

;

load

Team,

Sum(ActualNbr) as Actual,

Sum(FTENbr) as FTE

group by Team

;

load

Team,

if(Type='Actual',Number,0) as ActualNbr,

if(Type='FTE',Number,0) as FTENbr

resident Data

;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

It would give you an output like this.

Capture.PNG