topic Re: Can I use load inline to create a mapping from numeric month to month name in QlikView

Can I use load inline to create a mapping from numeric month to month name

Wed, 21 Jun 2017 17:05:04 GMT

I am creating some filters for my form using listboxes. one of the listboxes is pulling from month field, which has the format of MM/DD/YYYY. I would like to change it so it gives the 3 letter acronym instead like Jan. can I use an inline load table to create a mapping to use in the listbox?

Thanks,

Mike

Re: Can I use load inline to create a mapping from numeric month to month name

Anonymous — Wed, 21 Jun 2017 17:08:52 GMT

date(date#(FIELD,'MM/DD/YYYY'),'MMM')

Re: Can I use load inline to create a mapping from numeric month to month name

sunny_talwar — Wed, 21 Jun 2017 17:09:55 GMT

Although you can, you don't really need to do this... You can just do this

Month(DateField) as Month

and then use Month field as your list box field

Re: Can I use load inline to create a mapping from numeric month to month name

antoniotiman — Wed, 21 Jun 2017 17:10:19 GMT

Hi Mike,

You don't need Mapping Table and ApplyMax.

Sumply in script :

Month(DateField) as Month

Month is Dual Field (Text,Num)

Text(Month) -> Jan

Num(Month) -> 1

Regards,

Antonio

Re: Can I use load inline to create a mapping from numeric month to month name

Wed, 21 Jun 2017 17:24:42 GMT

One thing I forgot to mention, I am first using a qlikview worksheet to load the data ignto a qvd file, and then my main dashboard file just loads the qvd file. the script in the load file is:

TBL_PNL:

LOAD BUS_UNIT,

SOURCE_TYPE,

MONTH,

EXPENSE_TYPE,

LINE_TYPE,

LINE_SUBTYPE,

AMOUNT,

EXPN_VAL

FROM

P_L_Report_Updatedv2.xlsx

(ooxml, embedded labels, table is Template);

STORE TBL_PNL into '..\PNL Dashboard\PNLData.qvd';

and then I am just loading that PNLData.qvd in my load script on the main file. i still need the MONTH column displayed as the MM/DD/YYYY format in my pivot table as well. Do I need to do these extra statements in my load file I am assuming? Is there a way I can pull MONTH twice and have one run that date conversion you mentioned above?

Re: Can I use load inline to create a mapping from numeric month to month name

Wed, 21 Jun 2017 17:44:56 GMT

Ok, I got it to work. I put that code in like you suggested into my load dashboard and then loaded that new field into my design dashboard. Thanks for the help!