Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
villegasi03
Creator
Creator

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
Master II
Master II

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

View solution in original post

3 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Master II
Master II

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
Creator
Creator
Author

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