Value List for Bins?

[Anonymous]

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

[Anil_Babu_Samineni · Accepted solution]

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

)))))

[luismadriz]

Hi Jeff,

Try this:

1. Create a table in Qlik Sense
2. In Dimension write something like this =AGGR(Count(TransactionField),IDField)
3. In Measure write =Count(IDField)
4. This should give you the whole breakdown of how many transactions with how many IDs
5. You can convert that table into a Bar charts

Now to limit your table to only 1,,4,>4

You could do this:

1. Create another table
2. In Dimension write something like 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)))))

1. In Measure write =Count(IDField)
2. This should give you the breakdown for the selected frequencies
3. And you can drag a Bar chart on it and convert it

I hope this helps,

Give and go and let us know,

Cheers,

Luis

[luismadriz]

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