How to make/derive new dimensions using existing values in a dimension

[tanmay_bhardwaj]

I have an existing dimension in my data model comprising of 50 + values. Based on these values I want to derive a new Dimension (by creating a Master Dimension) which contains a smaller list of values.

Each of this new value is derived as indicated in the table below:

How can I write a SET analysis query to solve this problem. 

Thanks

Environment: Qlik Sense Enterprise Client Managed for Windows

Version: QlikSense May 2023

[Aditya_Chitale]

For this you will have to first find out why you want to group Only Old category 1 upto 4 in New Category 1 and likewise for remaining data.

Once you have determined the field on basis of which you have to group and calculate new dimension, use below set expression as a dimension in your table:

='New Category ' & aggr(nodistinct(rowno()),distinguishing_field)

Sample data used:

test:
load * Inline
[
old_dimension, distinguishing_field
OldCategory1,ABC
OldCategory2,ABC
OldCategory3,ABC
OldCategory4,ABC
OldCategory5,XYZ
OldCategory6,XYZ
OldCategory7,PQR
OldCategory8,PQR
OldCategory9,PQR
OldCategory10,PQR
];

Output:

Regards,

Aditya

[Or]

This would typically be handled in script, not in a Master Dimension.

Load * From OriginalTable;

JOIN

Load [Category 1], [Category 2] From SomeFile;

With Category 2 being your new dimension (and can be used with a master item).

If you absolutely need this to be done at the master item level, you'd just need to write a really long Pick(Match()) statement along the lines of:

Pick(Match([Category 1],'Old Category 1', 'Old Category 2'),'New Category 1', 'New Category 1') etc.

I would not recommend this approach, though. Doing it in script is almost certainly a better approach.

[Aditya_Chitale]

Yes that's the challenging part. How many pick  match statements will you write ? Instead  if the OP wants autonumbered dimension,  we can use rowno()  function. 

Regards,

Aditya

[Or]

---

@Aditya_Chitale wrote:

Yes that's the challenging part. How many pick  match statements will you write ? Instead  if the OP wants autonumbered dimension,  we can use rowno()  function. 

Regards,

Aditya

---

I didn't see any request for an autonumbered version, and there wasn't a distinguishing field available in the sample information provided. It just looks like a simple join (using an Excel file or inline) or mapping load using the same source, either would work but a join is easier to explain.

[Tanish]

Hi Tanmay

If you create a dimension in Set Analysis, it will make the front end slow instead of this
Let me tell the simplest ways to achieve this result, you can create a one excel with only two fields
Category1 , Category2
Link this table with the main table. Then directly use this Category2 in front end.

Hope this will work..........🙂