Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
SusanneScherer
New Contributor II

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).

clipboard_image_2.png

 

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.

clipboard_image_3.png

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

 

 

Tags (2)
8 Replies
MVP & Luminary
MVP & Luminary

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

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
New Contributor II

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

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
New Contributor II

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

To answer your question more specifically:

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

SusanneScherer
New Contributor II

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

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.

MVP & Luminary
MVP & Luminary

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

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:

RegionAug19Jul19Jun19
A100110120
B200210220

 

to this

RegionMonthAmount
AJun19120
AJul19110
AAug19100
BJun19220
BJul19210
BAug19200

 

is this the type of data you are dealing with?

-Rob

SusanneScherer
New Contributor II

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

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_NAMEACCT_IDSA_IDSA_TYPE_CDMonth /Time PeriodAmount
LMSL43395100014339510641EC-TOTALPREVIOUS_MONTH$606,214.63
LMSL43395100014339510641EC-TOTALPREVIOUS_MONTH_1$621,730.22
LMSL43395100014339510641EC-TOTALPREVIOUS_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?

 

MVP & Luminary
MVP & Luminary

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

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
New Contributor II

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

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.