Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Custom Dimension Groups

I have 2 hierarchies for customers, NAL2 and NAL3. I have more than 500 in NAL2 and 5000 in NAL3. The requirement is I need to build a Tracker for a Group of NAL2 and some under this for NAL3 in a Pivot. Currently I am doing this using the Valuelist and by a calculated Dimension. But its taking a lot of time as the chart has many calculated dimesions and If statements. Can you suggest a better way to do this? As in how can I do a Grouping of NAL2 and NAL3 customers and create a Dimension at the script level, so that All i have to do is use this Dimension and then write a simple expression to calclucalte the sales for a period...Hope you undestood the issue, The below is the sample set analysis query I am using for one customer, and I will send you more info if required.

if(ValueList('BOOTS')='BOOTS',sum({$< NPD_ID = {'NULL'},TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'},NAL2_ID = {'6170'}, NAL3_ID = {'9316'}, FORECAST_TYPE = {'CURRENT'} >}FCT_GROSS_REVENUE)+sum({$< TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'},NAL2_ID = {'6170'}, NAL3_ID = {'9316'}>}WK_SALES_GROSS_REV),

Like above I have a grouping of 45 customers and 4 different Pivots to show the details, I want to create this gouping at the script level and use the customer names as a Dimension to check if that increases the performance..

Thanks in Advance,

ANDY

Replied BY MIGUEL:

Hello Andy,

Since the question is different and seems not related to the current one, I recommend you to create a new thread in the forum with the new question.

Besides, I'm not quite sure to understand waht you need. Are those hierarchies NAL2 and NAL3 actual fields in your tables? If so, why do you need to use a synthetic dimension (using ValueList). Or is that you only have a code (NAL3_ID) and you want to create a text for each ID? Is there any relationship between NAL2 and NAL3 (I assume there is at least one, the customer ID)?

I think that you can use a pivot table with NAL2 and NAL3 and dimensions, and one expression for each forecast and actual sales.

Is that right?

8 Replies
Not applicable
Author

Are those hierarchies NAL2 and NAL3 actual fields in DB? YES

If so Why do you need a synthetic dimension? As I am unable to get the formatting of the existing report the Client has, something which looks like below image here.

Or is that you have a code NAL3_ID and you want to create a text for each ID? I have 2 codes NAL2 and NAL3, and they are linked together as hierarchy in the DB and every NAL2 has many NAL3

Please check the image here and suggest a solution. Thanks in advance,

Miguel_Angel_Baeyens

Hi,

If each value you are showing above (BOOTS, BP, etc) is the result of a combination of NAL2 and NAL3 values, then I'd create this groups in the load script using, for example, a mapping table, so:

CustomGroupMap:MAPPING LOAD NAL2 & '/' & NAL3 AS NAL2_3 CUSTOM_GROUP_NAME // this must exists, maybe creating a support excel file, or an inline tableFROM File.qvd (qvd); Customers:LOAD *, ApplyMap(CustomGroupMap, NAL2 & '/' & NAL3, 'No Custom Group') AS CUSTOM_GROUPFROM Customers.qvd (qvd);


Now use that CUSTOM_GROUP field as dimension in your chart.

Hope that helps

Not applicable
Author

Thank you for the solution Miguel,

I would try this for sure and let you know how it went.

ANDY

Not applicable
Author

Hello Miguel,

In your above solution, should i write the Mapping Load NAL2 and NAL3 statement for each customer set. For example : I have NAL2_ID = '6175','6176','6177' and NAL3_ID = '9321','9322','9323' , So I am looking to join these and then name it as FOOD SERVICE FG. And similarly I have NAL2_ID = 6262 and NAL3_ID = 9234 and want to call it as BOOTS. But otherwise I have more than 10 NAL3 IDs for each NAL2 associated.

So if I write MAPPING LOAD NAL2 & '\' NAL3 AS NAL2_3, that would join all the Nal2s with Nal3 right? For this should I create a Excel sheet externally with a NAL2 column where I have all the IDs and a NAL3 column with IDs and then do a CUSTOM GROUP NAME...? After that write my actual Customer Load statement from the DB?? Can you please take some time to explain me more about this? You even have the screenshot of what I am trying to achieve...

Thanks Again,

ANDY

Miguel_Angel_Baeyens

Hello Andy,

Kind of, yes. According to your example, and if I got your requirement right, the excel file should look like

NAL2_ID, NAL3_ID, CUSTOM_GROUP_NAME'6175', '9321', 'FOOD SERVICE FG''6175', '9322', 'FOOD SERVICE FG''6175', '9323', 'FOOD SERVICE FG''6176', '9321', 'FOOD SERVICE FG''6176', '9322', 'FOOD SERVICE FG''6176', '9323', 'FOOD SERVICE FG''6177', '9321', 'FOOD SERVICE FG''6177', '9322', 'FOOD SERVICE FG''6177', '9323', 'FOOD SERVICE FG''6262', '9234', 'BOOTS'


Now you can load these values in the MAPPING table and use them with the APPLYMAP() function.

Hope that helps.

Not applicable
Author

Hello Miguel,

I was trying the solution as suggested by you, but its giving me errors as CustomGroupMap field not found and also in your previous post you mentioned i need to add ApplyMap (CustomGroupMap, NAL2_ID & '/' & NAL3_ID, 'No Custom Group') AS CUSTOM_GROUP, What is the No Custome Group you meant by?? I have the actual script as seen below can you please take a look at this and help me further?

CustomGroupMap:
MAPPING LOAD NAL2_ID & '/' & NAL3_ID AS NAL2_3,
[Account Grouping]
FROM
(ooxml, embedded labels, table is Sheet1);

W_ONVDWH_DELIVERY_POINT_DENORM:

LOAD
ApplyMap (CustomGroupMap, NAL2_ID & '/' & NAL3_ID) AS CUSTOM_GROUP,
TERRITORY_CODE,
TERRITORY_DESC,
TERRITORY_SORT_KEY,
AREA_CODE,
AREA_DESC,
AREA_SORT_KEY,
DIVISION_CODE,
DIVISION_NAME,
DIVISION_SORT_KEY,
SALES_FORCE_CODE,
SALES_FORCE_DESC,
SALES_FORCE_SORT_KEY,
NAL4_CODE,
NAL4_DESC,
NAL3_CODE,
NAL3_DESC,
NAL2_CODE,
NAL2_DESC,
ACCOUNT_DESC,
ACCOUNT_SORT_KEY,
SUB_CHANNEL_DESC,
SUB_CHANNEL_SORT_KEY,
CHANNEL_DESC,
CHANNEL_SORT_KEY,
SECTOR_DESC ,
SECTOR_SORT_KEY,
TEXT(DP_ID) AS DP_ID,
SALES_FORCE_ID,
NAL3_ID,
NAL2_ID,
SUB_CHANNEL_ID,
CHANNEL_ID,
DIVISION_ID,
NAL4_ID,
AREA_ID,
ACCOUNT_ID,
SECTOR_ID,
TERRITORY_ID
FROM
[W_ONVDWH_DELIVERY_POINT_DENORM.QVD](qvd);

Thanks a Ton, ANDY

Not applicable
Author

Hello Miguel,

Can you please take a look at this and let me know more about this please.. I created Excel sheeet as suggested by you and i have the script as seen here and I think I am confused about the 'No Custom Group' field, but its leading me to nowhere... any help would be greatly appreciated by me and my client 🙂

Thanks Again,

ANDY

Miguel_Angel_Baeyens

Hello Andy,

These are the three parameters used by ApplyMap, you can check them further in the Reference Manual that comes with QlikView installation:

ApplyMap('CustomGroupMap', NAL2_ID & '/' & NAL3_ID, 'No Custom Group')


First must be single quoted and is the name of the table that loads the mapping table you are "calling", in the example above 'CustomGroupMap',

Second is the parti in the current table for which you want to get the correspondent in the mapping table, in this case, NAL2_ID & '/' & NAL3_ID.

Third is the expression used when no correspondence for the previous parameter is found, in this case a text that shows 'No Custom Group'. This will only show when the current record NAL2_ID & '/' & NAL3_ID has no correspondence with any of the mapping table records.

It would be easier if you could post some example data, but your code above seems good to me, except for the quoting in the mapping table label (as in the example, the bold part)

Hope that helps.