
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using Mapping Load / Apply Map to get a Month Name - does not work
Hi everyone,
In one table I have running monthly names ("PREVIOUS_MONTH_1", "PREVIOUS_MONTH_2", ...) as field names.
I am trying to get the matching MONTH_YYYY name from a reference table (For example AUG-2019 for PREVIOUS_MONTH).
I am using Mapping Load / Apply Map as follows (see below). It loads without error but does not return the matching month. Instead of MONTH_YYYY it should be AUG-2019.
Can someone please explain how I get this to work? Where is my mistake?
MonthLookUpMap:
LIB CONNECT TO 'DWHDEV (ouc_rosan030)';
MAPPING LOAD "MONTH_LABEL" ,
"MONTH_YYYY" ;
SQL SELECT "MONTH_LABEL",
"MONTH_YYYY"
FROM BIREPORT."CM_LOOKUP_MONTHS_MV";
CM_QLIK_CU_0001_MV:
LIB CONNECT TO 'DWHDEV (ouc_rosan030)';
LOAD "ACCT_ID",
"SA_ID",
"SA_TYPE_CD",
"BILL_CYC_CD",
"CUST_CL_CD",
"ENTITY_NAME",
RUNDATE,
"AVG_MON_BILL_LAST_12MONTHS",
"TOT_BILL_AMT_L12MONTHS",
"AVG_MON_BILL_13TO24_MONTHS",
"TOT_BILL_AMT_13TO24_MONTHS",
"TOTAL_AMT_BILL_CALENDAR",
"TOTAL_AMT_BILL_FISCAL",
"PREVIOUS_MONTH",
ApplyMap('MonthLookUpMap', PREVIOUS_MONTH) as [MONTH_YYYY],
"PREVIOUS_MONTH_1",
"PREVIOUS_MONTH_2",
"PREVIOUS_MONTH_3",
"PREVIOUS_MONTH_4",
"PREVIOUS_MONTH_5",
"PREVIOUS_MONTH_6",
"PREVIOUS_MONTH_7",
"PREVIOUS_MONTH_8",
"PREVIOUS_MONTH_9",
"PREVIOUS_MONTH_10",
"PREVIOUS_MONTH_11",
"PREVIOUS_MONTH_12",
"PREVIOUS_MONTH_13",
"PREVIOUS_MONTH_14",
"PREVIOUS_MONTH_15",
"PREVIOUS_MONTH_16",
"PREVIOUS_MONTH_17",
"PREVIOUS_MONTH_18",
"PREVIOUS_MONTH_19",
"PREVIOUS_MONTH_20",
"PREVIOUS_MONTH_21",
"PREVIOUS_MONTH_22",
"PREVIOUS_MONTH_23",
;
SQL SELECT "ACCT_ID",
"SA_ID",
"SA_TYPE_CD",
"BILL_CYC_CD",
"CUST_CL_CD",
"ENTITY_NAME",
RUNDATE,
"AVG_MON_BILL_LAST_12MONTHS",
"TOT_BILL_AMT_L12MONTHS",
"AVG_MON_BILL_13TO24_MONTHS",
"TOT_BILL_AMT_13TO24_MONTHS",
"TOTAL_AMT_BILL_CALENDAR",
"TOTAL_AMT_BILL_FISCAL",
"PREVIOUS_MONTH",
"PREVIOUS_MONTH_1",
"PREVIOUS_MONTH_2",
"PREVIOUS_MONTH_3",
"PREVIOUS_MONTH_4",
"PREVIOUS_MONTH_5",
"PREVIOUS_MONTH_6",
"PREVIOUS_MONTH_7",
"PREVIOUS_MONTH_8",
"PREVIOUS_MONTH_9",
"PREVIOUS_MONTH_10",
"PREVIOUS_MONTH_11",
"PREVIOUS_MONTH_12",
"PREVIOUS_MONTH_13",
"PREVIOUS_MONTH_14",
"PREVIOUS_MONTH_15",
"PREVIOUS_MONTH_16",
"PREVIOUS_MONTH_17",
"PREVIOUS_MONTH_18",
"PREVIOUS_MONTH_19",
"PREVIOUS_MONTH_20",
"PREVIOUS_MONTH_21",
"PREVIOUS_MONTH_22",
"PREVIOUS_MONTH_23"
FROM BIREPORT."CM_QLIK_CU_0001_MV";


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I kind of understand your question and then again I don't. I see what you are doing with the applymap. What I don't understand is the chart measure Sum(MONTH_YYYY). Are you expecting that the applymap will change this measure?
-Rob

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for your quick reply.
I am just trying to get the matching month and year as the field name. The matching month is located in a materialized view which will be updated monthly.
Instead of PREVIOUS_MONTH it should say Aug 19, instead of "PREVIOUS_MONTH_1" it should say Jul 19, instead of "PREVIOUS_MONTH_3" it should say Jun 19 etc. It should be dynamic, not hard coded somewhere, because it will change whenever a new month starts.
I am not tied to this specific solution, anything that works would is fine. I was even thinking if there was a dynamic way to change the label on the front end (with set analysis?) that would work, too?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
To answer your question more specifically:
Yes, Instead of Sum(MONTH_YYYY) I expected to see Sum(Aug 2019).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The value of field "MONTH_YYYY" (for example Aug 2019) from table MonthLookUpMap should become an alias field name for the matching month field name (for example PREVIOUS_MONTH) in table CM_QLIK_CU_0001_MV.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ApplyMap() will change the values of the field, not the fieldname. Instead, to change your fieldnames using the map, add this statement to the end of your script (or somewhere after the table load):
Rename Fields Using MonthLookUpMap;
That will rename your fields to the names you loaded into the map.
If you want your expressions to reference the new fieldname, it gets a little more complex. For example you want "Sum(PREVIOUS_MONTH)" to become "SUM(AUG_2019)" which will change dynamically as the months change. You can't change the text of the expression directly from the script, but you can use a variable set by the script and reference that in the expression like this:
Sum($(vPreviousMonth))
Before I give a specific script example on how to set the variable, let me take a step back and ask a bit more about your data structure. Your input is a crosstable, and typically the best technique is to use a uncross the columns into rows uisng a CrossTable load and then deal with your expressions from there. It makes things much easier in Qlik. So you go from this:
Region | Aug19 | Jul19 | Jun19 |
A | 100 | 110 | 120 |
B | 200 | 210 | 220 |
to this
Region | Month | Amount |
A | Jun19 | 120 |
A | Jul19 | 110 |
A | Aug19 | 100 |
B | Jun19 | 220 |
B | Jul19 | 210 |
B | Aug19 | 200 |
is this the type of data you are dealing with?
-Rob

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, you are correct. The data is in a pivot table structured very similar to your example.
Uncrossed it could look something like that:
ENTITY_NAME | ACCT_ID | SA_ID | SA_TYPE_CD | Month /Time Period | Amount |
LMSL | 4339510001 | 4339510641 | EC-TOTAL | PREVIOUS_MONTH | $606,214.63 |
LMSL | 4339510001 | 4339510641 | EC-TOTAL | PREVIOUS_MONTH_1 | $621,730.22 |
LMSL | 4339510001 | 4339510641 | EC-TOTAL | PREVIOUS_MONTH_2 | $579,937.32 |
As far as I understand it was pivoted for the Materialized View used to load it into Qlik. It contains about 1,6 M rows with distinct SA_ID.
It has 30 different amount fields.
"AVG_MON_BILL_LAST_12MONTHS",
"TOT_BILL_AMT_L12MONTHS",
"AVG_MON_BILL_13TO24_MONTHS",
"TOT_BILL_AMT_13TO24_MONTHS",
"TOTAL_AMT_BILL_CALENDAR",
"TOTAL_AMT_BILL_FISCAL",
"PREVIOUS_MONTH",
"PREVIOUS_MONTH_1",
"PREVIOUS_MONTH_2",
"PREVIOUS_MONTH_3",
"PREVIOUS_MONTH_4",
"PREVIOUS_MONTH_5",
"PREVIOUS_MONTH_6",
"PREVIOUS_MONTH_7",
"PREVIOUS_MONTH_8",
"PREVIOUS_MONTH_9",
"PREVIOUS_MONTH_10",
"PREVIOUS_MONTH_11",
"PREVIOUS_MONTH_12",
"PREVIOUS_MONTH_13",
"PREVIOUS_MONTH_14",
"PREVIOUS_MONTH_15",
"PREVIOUS_MONTH_16",
"PREVIOUS_MONTH_17",
"PREVIOUS_MONTH_18",
"PREVIOUS_MONTH_19",
"PREVIOUS_MONTH_20",
"PREVIOUS_MONTH_21",
"PREVIOUS_MONTH_22",
"PREVIOUS_MONTH_23"
Do I understand it correctly that if you uncross it you would get 1,6 M rows * 30 = about 48 Million Rows?
Would you still recommend this as best practice?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, in general, I would still recommend uncrossing as a best practice. It's still the same amount of data and it will make the model much easier to use.
You can use your month map as a calendar table rather than a mapping table. Then you can use expressions like:
=Only({<[Month/Time Period]={'PREVIOUS MONTH'}>} MONTH_LABEL) // Month Name
=Sum({<[Month/Time Period]={'PREVIOUS MONTH'}>}Amount) // Sum for last month
Please do consider eliminating any months you don't really need. For example, if you are going to display only the prior 6 months don't load the older data.
You mentioned that the pivoted view was created for Qlik. If you find you are spending time un-pivoting it in Qlik, you may want to revisit the view.
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rob,
Thank you again. Your reply was very insightful for me.
The longer term solution for us will probably be to change the view to a uncrossed format.
