14 Replies Latest reply: Oct 10, 2016 9:40 PM by Samuel Lin RSS

    Qliksense Custom Dimension in Straight Table

    Samuel Lin

      Hello Qlik Geeks...

       

      I am trying to create a table with this view:

       

      Capture.PNG

       

      I used this dual function to have my own custom dimension but realized that these are if statements; let's assume that these are not necessarily mutually exclusive (i.e. it's possible that a customer is part of multiple "groups/segments"; so the if statements that I had wouldn't work.

       

      Here's my current codes for dimension:

      dual(

      if([have a current lifetime value]>0, 'All',

      if([L12MRevenueSegment] = '1 - High Value', 'High Value',

      if([TotalOrderCount]=1, 'One-Time Buyers',

      if(FABS([last order date]-(today()-274))<91  , 'At-Risk',

      if([last order date]<=(today()-365), 'Lapsed',

      )))))

      ,

      if([have a current lifetime value]>0, 1,

      if([L12MRevenueSegment] = '1 - High Value', 2,

      if([TotalOrderCount]=1, 3,

      if(FABS([last order date]-(today()-274))<91 , 4,

      5))))

      )

      anyone has any idea how I can create my own custom dimensions?

       

      Thank you very much!!

      Samuel

        • Re: Qliksense Custom Dimension in Straight Table
          Martin Mahler

          Don't use multiple if statements - super slow and hard to maintain.

           

          See the example in the following link of an alternative impementation of your requirement:

          Re: Re: Variable as a Field in Table

            • Re: Qliksense Custom Dimension in Straight Table
              Samuel Lin

              Thank you Martin! I have been googling and trying to find helpful resources on pick and match... and I haven't found something solid to guide me through how to write my pick and match. Do you have any resource links for pick and match? especially using them together?

               

              and yes, my qlik UI has been slowed down VERY much because I didn't know other options. Once I learned how to use pick and match, hopefully will be faster.

               

              Many thanks,

              • Re: Qliksense Custom Dimension in Straight Table
                Samuel Lin

                Also, Martin, I think my case may be a little different? I am using multiple fields (column headers) to put together a new dimension... do you think that's still possible?

                  • Re: Qliksense Custom Dimension in Straight Table
                    Martin Mahler

                    That's fine. In the example of the other post (Re: Re: Variable as a Field in Table) you can specify unique expression per row, effectively creating something like a KPI table.

                      • Re: Qliksense Custom Dimension in Straight Table
                        Samuel Lin

                        Hey Martin, Please correct me if I am wrong. From the example, does that mean I need to have the table created first? before I can use Pick() and Match()? Do I need to have a table in the UI or a table in the backend script?

                         

                        Also, the example is only evaluating with myVal, whereas, I am calculating from multiple value sources/dimensions (the original post), is that possible?

                         

                        Thank you so much for your help!

                        Samuel

                          • Re: Qliksense Custom Dimension in Straight Table
                            Martin Mahler

                            Hey Samuel,

                             

                            Have had a second look at your requirement, I think what you are trying to do is to segment your clients and put them into separate buckets based on various criteria.

                             

                            I believe this is not something that should be done on the front-end but rather in the loading script instead.

                            Therefore you should take the multiple if-statement and use it in the load script to create a new flag called "Digital Segment". Once the new dimension is created, it will become straightforward to use the segmentation on the front end.

                              • Re: Qliksense Custom Dimension in Straight Table
                                Samuel Lin

                                Thanks, Martin, Agree with you on that! Given that each unique user may be qualified for multiple segment, how would you go after using the if statements with the end goal that they will be under the same "column"?     

                                • Re: Qliksense Custom Dimension in Straight Table
                                  Samuel Lin

                                  Thank you Martin for your help and recommendation!

                                   

                                  I have bring all my segmentation from front-end implementation to the back-end, and the UI front-end is much much much faster now, thank you so much!

                                   

                                  I did something like this for the segmentation in Script Load:

                                   

                                  DigitalSegment:

                                  Load if([last order date]  > (today()-730),[EmailAddress]) as EmailAddress,

                                    'All' as DigitalSegment

                                  Resident [Full_List_Order_History];

                                  Concatenate

                                  Load if([L12MRevenueSegment] = '1 - High Value', [EmailAddress]) as EmailAddress,

                                    'High Value' as DigitalSegment

                                  Resident [Full_List_audience-1];

                                  Concatenate

                                  Load if([TotalOrderCount]=1, [EmailAddress]) as EmailAddress,

                                    'One-Time Buyers' as DigitalSegment

                                  Resident [Full_List_audience-1];

                                  Concatenate

                                  Load if([last order date] > (today()-365) and [last order date] < (today()-183), [EmailAddress]) as EmailAddress,

                                    'At-Risk' as DigitalSegment

                                  Resident [Full_List_Order_History];

                                  Concatenate

                                  Load if([last order date] <= (today()-365), [EmailAddress]) as EmailAddress,

                                    'Lapsed' as DigitalSegment

                                  Resident [Full_List_Order_History];

                                  Concatenate

                                  Load if([AtLeastOnceAR] = 'AtLeastOnceAR', [EmailAddress]) as EmailAddress,

                                    'Auto Replenish' as DigitalSegment

                                  Resident [At_Least_Once_AR];

                                   

                                  This has help my front-end UI to run faster, but just want to pick your brain and see if you think this is the most efficient way to create dimension? If you don't have time, that's alright!

                                   

                                  I will soon close the discussion and mark one of your response as the correct answer

                                   

                                  Thank you!

                                  Samuel

                              • Re: Qliksense Custom Dimension in Straight Table
                                Samuel Lin

                                Martin,

                                 

                                One clarification, besides creating dimension for the table, I am also hoping to use the newly created dimension for filter panels as well.

                                 

                                Right now, without completely understand what I am doing, I am creating Load Inline tables in the backend load script and am trying to use that to get pick and match going in the front end. any feedback on the direction I am heading?

                                 

                                Thanks,

                            • Re: Qliksense Custom Dimension in Straight Table
                              Samuel Lin

                              and any smart hint to transform this to a pick and match? just a hint, I'd like to figure this out...  

                               

                              this is what I am using for a filter panel, and like you said, it has made my qlik so much slower!!!!

                               

                               

                              dual(

                              if(aggr(avg([order frequency]),EmailAddress)>400, '>400',

                              if(aggr(avg([order frequency]),EmailAddress)>350, '350~400',

                              if(aggr(avg([order frequency]),EmailAddress)>300, '300~350',

                              if(aggr(avg([order frequency]),EmailAddress)>250, '250~300',

                              if(aggr(avg([order frequency]),EmailAddress)>200, '200~250',

                              if(aggr(avg([order frequency]),EmailAddress)>150, '150~200',

                              if(aggr(avg([order frequency]),EmailAddress)>100, '100~150',

                              if(aggr(avg([order frequency]),EmailAddress)>50, '50~100',

                              if(aggr(avg([order frequency]),EmailAddress)>25, '25~50',

                              if(aggr(avg([order frequency]),EmailAddress)>0, '0~25',

                              if(aggr(avg([order frequency]),EmailAddress)=0, '0',

                               

                               

                               

                               

                              )))))))))))

                              ,

                              if(aggr(avg([order frequency]),EmailAddress)>400, 1,

                              if(aggr(avg([order frequency]),EmailAddress)>350, 2,

                              if(aggr(avg([order frequency]),EmailAddress)>300, 3,

                              if(aggr(avg([order frequency]),EmailAddress)>250, 4,

                              if(aggr(avg([order frequency]),EmailAddress)>200, 5,

                              if(aggr(avg([order frequency]),EmailAddress)>150, 6,

                              if(aggr(avg([order frequency]),EmailAddress)>100, 7,

                              if(aggr(avg([order frequency]),EmailAddress)>50, 8,

                              if(aggr(avg([order frequency]),EmailAddress)>25, 9,

                              if(aggr(avg([order frequency]),EmailAddress)>0, 10,

                              11))))))))))

                              )

                                • Re: Qliksense Custom Dimension in Straight Table
                                  Martin Mahler

                                  Have a look at the class() function

                                    • Re: Qliksense Custom Dimension in Straight Table
                                      Samuel Lin

                                      Hi Martin,

                                       

                                      Thank you so much for your help so far. For this example above:

                                      dual(

                                      if(aggr(avg([order frequency]),EmailAddress)>400, '>400',

                                      if(aggr(avg([order frequency]),EmailAddress)>350, '350~400',

                                      if(aggr(avg([order frequency]),EmailAddress)>300, '300~350',

                                      if(aggr(avg([order frequency]),EmailAddress)>250, '250~300',

                                      if(aggr(avg([order frequency]),EmailAddress)>200, '200~250',

                                      if(aggr(avg([order frequency]),EmailAddress)>150, '150~200',

                                      if(aggr(avg([order frequency]),EmailAddress)>100, '100~150',

                                      if(aggr(avg([order frequency]),EmailAddress)>50, '50~100',

                                      if(aggr(avg([order frequency]),EmailAddress)>25, '25~50',

                                      if(aggr(avg([order frequency]),EmailAddress)>0, '0~25',

                                      if(aggr(avg([order frequency]),EmailAddress)=0, '0',

                                       

                                      )))))))))))

                                      ,

                                      if(aggr(avg([order frequency]),EmailAddress)>400, 1,

                                      if(aggr(avg([order frequency]),EmailAddress)>350, 2,

                                      if(aggr(avg([order frequency]),EmailAddress)>300, 3,

                                      if(aggr(avg([order frequency]),EmailAddress)>250, 4,

                                      if(aggr(avg([order frequency]),EmailAddress)>200, 5,

                                      if(aggr(avg([order frequency]),EmailAddress)>150, 6,

                                      if(aggr(avg([order frequency]),EmailAddress)>100, 7,

                                      if(aggr(avg([order frequency]),EmailAddress)>50, 8,

                                      if(aggr(avg([order frequency]),EmailAddress)>25, 9,

                                      if(aggr(avg([order frequency]),EmailAddress)>0, 10,

                                      11))))))))))

                                      )

                                       

                                      Do you mind explaining to me how would you load the if statement in the back end? would they be going to be on the same table? or would they just be on a separate load inline table? My apology if I am asking really newbie/stupid questions with newbie concepts of qlik...

                                       

                                      Thanks!!

                                • Re: Qliksense Custom Dimension in Straight Table
                                  Martin Mahler

                                  Given the fact your customers fall into multiple segments there is no way around creating a synthetic hierarchy which you impressively worked out yourself how to do in the load script.

                                   

                                  Ideally in a perfect world, the segmentation is provided to you upstream e.g. in a DB because you run the risk of maintaining too much logic in your Qlik script (which only you are able to understand ).

                                   

                                  Two tiny suggestions to improve your loading performance & RAM footprint

                                  1) If your load script takes ages, try storing your interim tables into QVD files and then loading from those directly rather than using Resident load. Controversially, resident loads are a tiny bit slower (preceding loads are the worst).

                                  2) In your example I assume EmailAddress is used as your key to your fact table. If you are not using the EmailAddress anywhere else, you might consider creating numeric IDs to link the tables using AutoNumber(EmailAddress) for example. They have a significantly lower RAM footprint. This only becomes relevant if you have millions of rows.