Using Mapping Load / Apply Map to get a Month Name - does not work

[SusanneScherer]

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";

[rwunderlich]

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

[SusanneScherer]

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?

[SusanneScherer]

To answer your question more specifically:

Yes, Instead of Sum(MONTH_YYYY) I expected to see Sum(Aug 2019).

[SusanneScherer]

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.

[rwunderlich]

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

[SusanneScherer]

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?

[rwunderlich]

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

[SusanneScherer]

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.