topic Help with Pick(Sign expression in QlikView

Help with Pick(Sign expression

helen_pip — Fri, 13 Oct 2017 13:13:02 GMT

Dear Qlikview user

I am trying to change an If statement into a Pick(sign expression as I know they are more efficient than an If statement

My expression is:

If(TB_Flag = 1 and

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric)))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2))

chr(9660),

If(TB_Flag = 1 and

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric)))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2))

chr(9658),

If(TB_Flag = 1 and

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric)))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2))

chr(9650)

My Attempt so far is:

=Pick(Sign(TB_Flag=1 and

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric)))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2)

+2,

chr(9660), chr(9658), chr(9650))))

I get an error message of Allocated Memory allocated and was wondering if someone could kindly help em write the expression

Any help would be greatly appreciated

Kind Regards

Helen

Re: Help with Pick(Sign expression

marcus_sommer — Fri, 13 Oct 2017 13:27:48 GMT

I think it should be rather look similar to this one:

=Pick(match(

Sign(

FirstSortedValue({<TB_Numerator={">=0"}, TB_Flag={1}>}

Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric),

-Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric))

FirstSortedValue({<TB_Numerator={">=0"}>}

Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric),

-Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2)),

-1,0,1)

chr(9660), chr(9658), chr(9650))

- Marcus

Re: Help with Pick(Sign expression

sunny_talwar — Fri, 13 Oct 2017 13:28:09 GMT

May be this

Pick(Match(TB_Flag &

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric)))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric)))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric)))

Num(FirstSortedValue({<TB_Numerator={">=0"}>}Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric), -Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2)), 1100, 1010, 1001), Chr(9660), Chr(9658), Chr(9650))

Re: Help with Pick(Sign expression

helen_pip — Fri, 13 Oct 2017 14:11:49 GMT

Hi Marcus

Thank you for your help, This has worked for me...Hopefully I can apply to most of my expressions now!

Kind Regards

Helen

Re: Help with Pick(Sign expression

helen_pip — Fri, 13 Oct 2017 14:12:54 GMT

Thank you for your help Sunny

The following expression worked for me on this occasion

=Pick(match(

Sign(

FirstSortedValue({<TB_Numerator={">=0"}, TB_Flag={1}>}

Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric),

-Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric))

FirstSortedValue({<TB_Numerator={">=0"}>}

Aggr(Sum(TB_Numerator), TB_Fact_Date, TB_Metric),

-Aggr(TB_Fact_Date, TB_Fact_Date, TB_Metric), 2)),

-1,0,1)

chr(9660), chr(9658), chr(9650))