topic Simplify the code if possible in QlikView

Simplify the code if possible

n1ef5ng1 — Wed, 11 Mar 2015 04:10:44 GMT

Hi all,

Below is a lengthy code that I have coded on the label of the chart as attached. It gives me the Alliance computed MOM%, YOY% and YTD% with extrapolation involved. I am thinking whether is there any ways to shorten the code and more efficient in maintaining. Thanks.

Some explanation on the codes below :

Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'}

means I am taking the latest month and today()-3 data... meaning today as of Mar 11, I am taking in 8 march data

/ Day( var_EndDate ) * Day(MonthEnd(MAX ( Date )))

This means extrapolation. I am dividing the volumes divide by Day(var_enddate) which is 8 * the number of days for this month to get extrapolation vol for March

=' Alliance
' & 'MOM: ' & (if( MonthEnd ( MAX( Date ) ) <> var_EndDate AND MonthEnd( MAX ( Date ) )
= MonthEnd( var_EndDate ) ,
(
Round(
(
(
(
sum({
<Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)
/ Day( var_EndDate ) * Day(MonthEnd(MAX ( Date )))
)
-
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)
/
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)

*100,0.1)
&'%'
)
,
(
Round(
(
(
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)
-
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-2)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-2)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-2)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-2)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)
/
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-2)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-2)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-2)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-2)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)
)
*100,0.1)
&'%'
)
))

& ' YOY: '
&

(if( MonthEnd ( MAX( Date ) ) <> var_EndDate AND MonthEnd( MAX ( Date ) )
= MonthEnd( var_EndDate ) ,
(
Round(
(
(
(
sum({
<Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)
/ Day( var_EndDate ) * Day(MonthEnd(MAX ( Date )))
)
-
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-12)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-12)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-12)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-12)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)
/
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-12)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-12)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-12)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-12)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)

*100,0.1)
&'%'
)
,
(
Round(
(
(
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)
-
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-13)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-13)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-13)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-13)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)
/
sum({
<Month_Id={"$(=max({<Month=>} Month_Id)-13)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-13)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-13)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Month_Id={"$(=max({<Month=>} Month_Id)-13)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)
)
*100,0.1)
&'%'
)
))

& ' YTD: '
&

(if( MonthEnd ( MAX( Date ) ) <> var_EndDate AND MonthEnd( MAX ( Date ) )
= MonthEnd( today() ) ,

(
Round(
(
(
(
sum({
<Year={"$(=max({<Year=>} Year))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Year={"$(=max({<Year=>} Year))"},Date ={'<=$(=Date(var_EndDate))'},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Year={"$(=max({<Year=>} Year))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Year={"$(=max({<Year=>} Year))"},Date ={'<=$(=Date(var_EndDate))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)
-
sum({
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)
/
sum({
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)

*100,0.1)
&'%'
)
,
(
Round(
(
(
sum({
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -12)))'},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)
-
sum({
<Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)

)
/
sum({
<Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>
+
<Date ={'<=$(=Date(AddMonths(var_EndDate, -13)))'},Year={"$(=max({<Year=>} Year)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>
}TEU)
)
*100,0.1)
&'%'
)
))

Re: Simplify the code if possible

jonathandienst — Wed, 11 Mar 2015 05:42:06 GMT

You need a bit more of the T* in your ETL to prepare your data for effective analysis. Take this clause for example:

sum({

<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘HUH’},CO_Operator_Group_Code={‘AA’,’AB’}>

<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},Consortium={‘MAO’},CO_Operator_Group_Code={‘AA’,’AB’},Service={'A20','AE3','A11','A12','FL3'}>

<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={‘AA1’,’AA2’,’AA3’,’AQ4’}>

<Month_Id={"$(=max({<Month=>} Month_Id)-1)"},CO_Operator_Group_Code={‘AA’,’AB’},Service={'OTH'},Vessel_Name={‘VES1’.’CSR1’,’FEFEWF’}>

}TEU)

In your load script, you can define the variable:

Set vMonthMinusOne = =(max({<Month=>} Month_Id)-1);

Then in your load statement, add the following code:

LOAD ...

If(Consortium = ‘HUH’ And Match(CO_Operator_Group_Code, 'AA’,’AB’)

Or (Consortium = ‘MAO’ And Match(CO_Operator_Group_Code, ‘AA’,’AB’) And Match(Service, 'A20','AE3','A11','A12','FL3'))

Or (Match(CO_Operator_Group_Code, ‘AA’,’AB’) And Match(Service, ‘AA1’,’AA2’,’AA3’,’AQ4’))

Or (Match(CO_Operator_Group_Code, ‘AA’,’AB’) And Service = 'OTH' And Match(Vessel_Name, ‘VES1’.’CSR1’,’FEFEWF’)), 1, 0) As Flag1,

...

Now that clause in the expression becomes:

Sum({<Month_Id = {'$(=vMonthMinusOne)'}, Flag1 = {1}>} TEU)

Repeat this exercise with the other clauses and date criteria....

HTH

Jonathan

*T = Transform

Re: Simplify the code if possible

marcus_sommer — Wed, 11 Mar 2015 11:10:15 GMT

Additional to the suggestion from jonathan dienst (you should do it first) - if you used some expression-varities often you could put some expression-parts into variables or you could use parametrized variables as expression, here an example:

//variable

eExpression:

sum({$1<$(=pick(match($2, 'd', 'mtd', 'm'),

'date = {"$(=date(max(date), 'DD.MM.YYYY'))"}',

'date = {">=$(=date(monthstart(max(date)), 'DD.MM.YYYY'))<=$(=date(max(date), 'DD.MM.YYYY'))"}',

'date = {">=$(=date(monthstart(max(date)), 'DD.MM.YYYY'))<=$(=date(monthend(date), 'DD.MM.YYYY'))"}'))>} $3)

//expression

$(eExpression(1, 'mtd', [Sales])) or maybe $(eExpression('', 'm', [Revenue]))

- Marcus