Help with Pick(Sign expression

[helen_pip]

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

[marcus_sommer · Accepted solution]

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

[sunny_talwar]

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))

[helen_pip]

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

[helen_pip]

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))