Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mp802377
Creator
Creator

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

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

Labels (2)
1 Solution

Accepted Solutions
edwin
Master
Master

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)

Capture.PNG

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

View solution in original post

1 Reply
edwin
Master
Master

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)

Capture.PNG

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