Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

samuel_lin
Contributor

Re: Qliksense Custom Dimension in Straight Table

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

Highlighted
samuel_lin
Contributor

Re: Qliksense Custom Dimension in Straight Table

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
Contributor

Re: Qliksense Custom Dimension in Straight Table

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
Valued Contributor III

Re: Qliksense Custom Dimension in Straight Table

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
Contributor

Re: Qliksense Custom Dimension in Straight Table

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

Community Browser