Straight table in QlikView where there are different expressions based on the value in the dimension

[mp802377]

Hello,

I am trying to create a straight table in QlikView where there are different expressions based on the value in the dimension. I saw someone use Pick(), but I couldn't get it to work here. I also tried using the 'IF' statements below. They didn't work. The dataset itself has probably 50 million rows (and a lot more columns - I am just listing applicable columns). It is a good-sized dataset. 

In the straight table, the dimension used it 'Flag'

In the expression area, the first expression is this:

Success:
IF(Flag = 1, (Num($(eSumSuccess), '#,##0'))
, IF(Flag = 4, (Num($(eSumSuccessPrice), '#,##0'))
, IF(Flag = 7, (Num($(eSumSuccessCreate), '#,##0'))
, IF(Flag = 10, (Num($(eSumSuccessSegment), '#,##0'))
))))

Failures:
IF (Flag = 2, (Num($(eSumFailure), '#,##0'))
, IF (Flag = 5, (Num($(eSumFailurePrice), '#,##0'))
, IF (Flag = 8, (Num($(eSumFailureCreate), '#,##0'))
, IF (Flag = 11, (Num($(eSumFailureSegment), '#,##0'))
))))

Attempts:
IF(Flag = 3, (Num($(eTry), '#,##0'))
, IF(Flag = 6, (Num($(eTryPrice), '#,##0'))
, IF(Flag = 9, (Num($(eTryCreate), '#,##0'))
, IF(Flag = 12, (Num($(eTrySegment), '#,##0'))
))))

RequestTypeDim:
LOAD * Inline[
~RequestTypeDim, Paramaters, Flag
Transaction, Success, 1
Transaction, Failures, 2
Transaction, Attempts, 3
Price, Success, 4
Price, Failures, 5
Price, Attempts, 6
Create, Success, 7
Create, Failures, 8
Create, Attempts, 9
Segment, Success, 10
Segment, Failures, 11
Segment, Attempts, 12

];

table from the dataset:
date,
customer,
request_type_desc,
count,
success,
failure,
success + failure as attempt
FROM customer.QVD (qvd);

sample of the dataset (I am putting in comas so you can clearly see the columns):
08/04/2020, Picker, Price, 1,1,0,1
08/09/2020, Nextgen, Create, 0,0,1,1
08/10/2020, Alliance, Segment, 4,4,0,4

Thank you for any help you can give!

Martha

[edwin · Accepted solution]

match and pick should still work.  i suggest you substitute your calculation in the pick and match instead of variable to see where the issue is.  also dont forget to include Flag in your dimension so the match will work (you can always hide it if its not needed)

 using the small data set, only transaction, Price and segment will have entries as Create has 0 success.