Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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,
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
Thank you for the solution Miguel,
I would try this for sure and let you know how it went.
ANDY
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
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.
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
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
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
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.