Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So first off, Super new to Qlik. I am using Qlik Sense. My experience is 10 years front end OBIEE Developer with some back end design work. I am ok with SQL (I get the job done) especially on the front end. Currently, I have access to the data load editor in Qlik, but I really need to do this "binning" on the front end.
So here is my ask/brick wall.
I have a huge list of IDs that have made transactions and in one view I want to group those views by volume. So (imagine bar chart) you would see the number of IDs that made 1, 2, 3, 4, or >4 transactions for the selected period of time. My support person usggested I use a Value List in Qlik Sense. What built was simple:
=ValueList('1', '2', '3', '4', '>4')
I then referenced the value list in a metric count.
What I am basically trying to do is the following:
IF(ValueList('1', '2', '3', '4', '>4')='1'.
Bring back the Count of all IDs that have Interactions =1 where that field that drives # of Interactions looks like this
Count( Distinct {<[transaction happened?]={1}>} [activity])
My challenge is what is the syntax? In SQL I could write a case statement where I would say give me the count of all ID where the # of transactions = 1
Any help is amazingly appreciated.
JC
You said, You are good in SQL. And this question related SQL (DB) only. So, Are you asking this question in QS? If, So
IF(ValueList('1', '2', '3', '4', '>4')='1', Count( Distinct {<[transaction happened?]={1}>} [activity]),
IF(ValueList('1', '2', '3', '4', '>4')='2', Measure,
IF(ValueList('1', '2', '3', '4', '>4')='3', Measure1,
IF(ValueList('1', '2', '3', '4', '>4')='4', Measdure2,
IF(ValueList('1', '2', '3', '4', '>4')='>4', Measdure3
)))))
You said, You are good in SQL. And this question related SQL (DB) only. So, Are you asking this question in QS? If, So
IF(ValueList('1', '2', '3', '4', '>4')='1', Count( Distinct {<[transaction happened?]={1}>} [activity]),
IF(ValueList('1', '2', '3', '4', '>4')='2', Measure,
IF(ValueList('1', '2', '3', '4', '>4')='3', Measure1,
IF(ValueList('1', '2', '3', '4', '>4')='4', Measdure2,
IF(ValueList('1', '2', '3', '4', '>4')='>4', Measdure3
)))))
Hi Jeff,
Try this:
Now to limit your table to only 1,,4,>4
You could do this:
=If(AGGR(Count(TransactionField),IDField) = 1,Dual('1',1),
If(AGGR(Count(TransactionField),IDField) = 2,Dual('2',2),
If(AGGR(Count(TransactionField),IDField) = 3,Dual('3',3),
If(AGGR(Count(TransactionField),IDField) = 4,Dual('4',4),Dual('>4',5)))))
I hope this helps,
Give and go and let us know,
Cheers,
Luis
Hi Jeff,
I just tested it on an App I'm working on and it works fine.
I made a little change to reduce the code. Instead of
=If(AGGR(Count(TransactionField),IDField) = 1,Dual('1',1),
If(AGGR(Count(TransactionField),IDField) = 2,Dual('2',2),
If(AGGR(Count(TransactionField),IDField) = 3,Dual('3',3),
If(AGGR(Count(TransactionField),IDField) = 4,Dual('4',4),Dual('>4',5)))))
You can use
=IF(AGGR(Count(TransactionField),IDField)<=4,AGGR(Count(TransactionField),IDField),Dual('>4',5))
Cheers,
Luis