Solved: Re: Qliksense Custom Dimension in Straight Table - Qlik Community

samuel_lin · ‎2016-10-04

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

simondachstr · ‎2016-10-09

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.

View solution in original post

simondachstr · ‎2016-10-04

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

samuel_lin · ‎2016-10-04

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,

samuel_lin · ‎2016-10-04

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?

samuel_lin · ‎2016-10-04

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

)

simondachstr · ‎2016-10-05

Have a look at the class() function

simondachstr · ‎2016-10-05

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.

samuel_lin · ‎2016-10-05

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

simondachstr · ‎2016-10-05

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.

samuel_lin · ‎2016-10-05

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,