topic Re: how to optimize Pick Match in QlikView

how to optimize Pick Match

Anonymous — Fri, 25 Aug 2017 04:15:15 GMT

How we can optimize below pick match function

Dimension -_List

Expression

=pick(match(_List,'Open','New','Closed','Closed Pending Review','Past Due','Extended Due Date','Full Downgrade','DPR'),
SUM( {<CalendarYear=,CalendarMonthName=,CalendarDay=, Link_DateKey={$(vMaxDate)},[Issue True Status]={'Addressing','Accepted'},Probability_Flag={'1'}>}Impact_Classification_Value)/1000000,
sum({< CalendarYear=,CalendarMonthName=,CalendarDay=,Link_DateKey=, [Created Date (Issue)]={">=$(vLMDate)<=$(vMaxDate)"},Probability_Flag={'1'}> }Impact_Classification_Value)/1000000,

if(GetSelectedCount(CalendarMonthName)=0 or GetSelectedCount(CalendarMonthName)=1,
sum({<CalendarYear=,CalendarMonthName=,CalendarDay=, Link_DateKey=,[Completion Date (Issue)]={">=$(vLMDate)<=$(vMaxDate)"},Probability_Flag={'1'}>} Impact_Classification_Value)/1000000,
sum({<CalendarYear=,CalendarMonthName=,CalendarDay=, Link_DateKey=,[Completion Date (Issue)]={">=$(vMin_DateNum)<=$(vMaxDate)"},Probability_Flag={'1'}>} Impact_Classification_Value))/1000000,
sum({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)},Link_DateKey={">=$(vMin_DateNum)<=$(vMaxDate)"},[Closed Pending Review]={'Yes'},[Issue True Status]={'*'}-{'Closed'}>}Impact_Classification_Value)/1000000,

sum( {<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, [Past Due]={'True'},Probability_Flag={'1'},Link_DateKey={">=$(vMin_DateNum)<=$(vMaxDate)"}>} Impact_Classification_Value)/1000000,
sum({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, Probability_Flag={'1'},Link_DateKey={$(vMaxDate)}>}Aggr(Only({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, Probability_Flag={'1'},Link_DateKey={$(vMaxDate)}>}Impact_Classification_Value)/1000000,RiskIssueID,_List))-
sum({<CalendarYear={$(vLM_Year)},CalendarMonthName={$(vLM)}, Probability_Flag={'1'},Link_DateKey={$(vLMDate)}>}Aggr(Only({<CalendarYear={$(vLM_Year)},CalendarMonthName={$(vLM)}, Probability_Flag={'1'},Link_DateKey={$(vLMDate)}>}Impact_Classification_Value)/1000000,RiskIssueID,_List)),

sum({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, DowngradeStatus={'T'},P2GDowngradeStatus={'T'},Probability_Flag={'1'},Link_DateKey={">=$(vMin_DateNum)<=$(vMaxDate)"}>}Impact_Classification_Value)/1000000,

sum({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, Link_DateKey={">=$(vMin_DateNum)<=$(vMaxDate)"},DPR={'Yes'},Probability_Flag={'1'}>} Impact_Classification_Value)/1000000)

Re: how to optimize Pick Match

el_aprendiz111 — Fri, 25 Aug 2017 19:30:09 GMT

From Script

Re: how to optimize Pick Match

felipedl — Fri, 25 Aug 2017 19:42:10 GMT

Not very helpful man, could at least propose something he could've done in the script to optimize it.

Re: how to optimize Pick Match

felipedl — Fri, 25 Aug 2017 19:44:34 GMT

Hi shweta,

What do you need with optimizing it?

If you need to control the expressions, so that it's somehow more readable, you could put a variable in between, with a name that states what the variable is doing.

Something like:

Expression

=pick(match(_List,'Open','New','Closed','Closed Pending Review','Past Due','Extended Due Date','Full Downgrade','DPR'),
$(sumOfValuesForSpecificReason1),
$(sumOfValuesForSpecificReason2),

$(sumOfValuesForSpecificReason3),

...

And so on.

Felipe.

Re: how to optimize Pick Match

Anonymous — Sun, 27 Aug 2017 02:56:55 GMT

I want to decrease the avg calculation time of the expression, not make it readable

Re: how to optimize Pick Match

vinieme12 — Sun, 27 Aug 2017 05:08:32 GMT

Try to create flags in script!

Re: how to optimize Pick Match

Anonymous — Mon, 28 Aug 2017 03:08:04 GMT

could you please explain.what do you mean by this

Re: how to optimize Pick Match

Anonymous — Mon, 30 Oct 2017 22:02:58 GMT

Not an expert.. maybe Pick(Match(Dimension,Value1,Value2,...Value8) takes longer to load in the UI than set analysis:

sum( {$<DimFlag={1}>} Value)

A pre-requisite step would be adding a _ListFlag column to the table with _List values - 'Open','New','Closed','Closed Pending Review','Past Due','Extended Due Date','Full Downgrade','DPR' - which essentially maps each value to 1.