Qliksense Custom Dimension in Straight Table

[samuel_lin]

Hello Qlik Geeks...

I am trying to create a table with this view:

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

[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!!

[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"?     

[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

[simondachstr · Accepted solution]

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.

[samuel_lin]

Great ideas, thank you Martin so much for your help!!