Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

villegasi03
Contributor

If Statements in Load Script vs. Chart Expressions

I have some QVDs that contain employee schedule activity details. My users need the details but I need to do a good bit of aggregations for high level stats. My problem is that the aggregations require the use of a ton of IF statements. I attempted perform the aggregations in the load script and then link it back to the details but I am seeing that you cant use if statements like you can in the chart expressions.

I was hoping that someone could provide some best practices for this type of situation. Should I do everything in the load script or should I do it in charts. It seems like if I do either I take a performance hit. Any ideas? Here is my epic first failed attempt script:

 

LOAD

SCHEDULEID,

IF(ISACTUAL = '0', MIN(SCHINTSTARTTIME)) AS AD_SCHEDULEDSTART,

IF(ISACTUAL = '1', MIN(SCHINTSTARTTIME)) AS AD_ACTUALSTART,

IF(ISACTUAL = '0', MAX(SCHINTSTOPTIME)) AS AD_SCHEDULEDSTOP,

IF(ISACTUAL = '1', MAX(SCHINTSTOPTIME)) AS AD_ACTUALSTOP,

IF(ISACTUAL = '0', MAX(SCHINTSTOPTIMELESS1)) AS AD_SCHEDULEDSTOPLESS1,

IF(ISACTUAL = '1', MAX(SCHINTSTOPTIMELESS1)) AS AD_ACTUALSTOPLESS1,

IF(SCH_ACTIVITYTYPENAME = 'ScheduleTime' AND ISACTUAL = '0', SUM(DURATION)) AS AD_SCHEDULEDACD,

IF(SCH_ACTIVITYTYPENAME = 'ScheduledTime' AND' AND ISACTUAL = '1', SUM(DURATION)) AS AD_ACTUALACD

FROM $(CustomerPath)\.qvd (qvd)

GROUP BY SCHEDULEID;

1 Solution

Accepted Solutions
er_mohit
Honored Contributor II

Re: If Statements in Load Script vs. Chart Expressions

Do it in the script for finding values but use min ,max in chart using aggr function  for SCHEDULEID

like in script

IF(ISACTUAL = '0', SCHINTSTARTTIME) AS AD_SCHEDULEDSTART,

IF(ISACTUAL = '1', SCHINTSTARTTIME) AS AD_ACTUALSTART,

(ISACTUAL = '0', MAX(SCHINTSTOPTIMELESS1)) AS AD_SCHEDULEDSTOPLESS1

etc....

in chart expression you 'll find

aggr(min(AD_SCHEDULEDSTART) ,SCHEDULEID)

aggr(max(AD_SCHEDULEDSTART), SCHEDULEID) istly try simple way to find min and max if didn't come then use aggr function for all

hope it helps you

3 Replies
MVP
MVP

Re: If Statements in Load Script vs. Chart Expressions

1. Preferably do it in the script, not in the charts, because of the performance issues.

2. If you have to aggregate this way, inlclude IF inside the Min/Max formulas, not the other way:

LOAD

SCHEDULEID,

MIN( IF(ISACTUAL = '0', SCHINTSTARTTIME) AS AD_SCHEDULEDSTART,

MIN( IF(ISACTUAL = '1', SCHINTSTARTTIME) AS AD_ACTUALSTART,

etc...

cheers,

Oleg Troyansky

er_mohit
Honored Contributor II

Re: If Statements in Load Script vs. Chart Expressions

Do it in the script for finding values but use min ,max in chart using aggr function  for SCHEDULEID

like in script

IF(ISACTUAL = '0', SCHINTSTARTTIME) AS AD_SCHEDULEDSTART,

IF(ISACTUAL = '1', SCHINTSTARTTIME) AS AD_ACTUALSTART,

(ISACTUAL = '0', MAX(SCHINTSTOPTIMELESS1)) AS AD_SCHEDULEDSTOPLESS1

etc....

in chart expression you 'll find

aggr(min(AD_SCHEDULEDSTART) ,SCHEDULEID)

aggr(max(AD_SCHEDULEDSTART), SCHEDULEID) istly try simple way to find min and max if didn't come then use aggr function for all

hope it helps you

villegasi03
Contributor

Re: If Statements in Load Script vs. Chart Expressions

Thanks everyone for the help. This was exactly what I needed.

Community Browser