If Statements in Load Script vs. Chart Expressions

[villegasi03]

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;

[Oleg_Troyansky]

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 · Accepted solution]

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]

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