3 Replies Latest reply: Apr 29, 2013 5:45 PM by Ismael Villegas II RSS

    If Statements in Load Script vs. Chart Expressions

    Ismael Villegas II

      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;