topic Re: Change data in a column in QlikView

Change data in a column

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a column called month, the months are being shown as numbers (1,2,3,4,5,6,7,8,9,10,11,12) but I would like the name to be shown (Jan, Feb , sea, apr, may, jun) how do I do this?

Ps: My database is an excel file

Ps: I use the qlik view trial version, I can not open qlik files from other users

Thank you

Re: Change data in a column

rubenmarin — Tue, 30 Oct 2018 16:11:05 GMT

Hi Nicolas, you can create a date using that month number and get the month from there

=Month(Makedate(Year(Today()),MonthFieldName))

Re: Change data in a column

jobsonkjoseph — Tue, 30 Oct 2018 16:30:51 GMT

Hi Nicolas,

Would you try this and see if it works;

map:

Mapping LOAD * INLINE [

Month1, Month

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, June

7, July

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec

];

table:

LOAD

applymap('map',Month) as Month,

FROM

[..\Downloads\data.xlsx]

(ooxml, embedded labels, table is data);

Re: Change data in a column

Anonymous — Tue, 30 Oct 2018 16:35:38 GMT

Hello Jobson, the solution worked but I would like to sort the months through the numerical field, is it possible?

Re: Change data in a column

Anonymous — Tue, 30 Oct 2018 16:36:06 GMT

Hello Ruben, the solution found for this problem is the creation of a map

Re: Change data in a column

jobsonkjoseph — Tue, 30 Oct 2018 16:55:00 GMT

Hi Nicolas,

I can check on that if sample data can be shared from your end.

Re: Change data in a column

Anonymous — Tue, 30 Oct 2018 17:02:25 GMT

As dimension I am using Map created, the field Per (Months of numeral form), status and the field "normal" is a sum of values

Re: Change data in a column

jobsonkjoseph — Tue, 30 Oct 2018 17:14:49 GMT

Dear Nicolas,

You need to use Date#() function in the Sort tab, i'm not able to come up with an expression for it.

Lets see if someone has an idea.

Re: Change data in a column

Anonymous — Tue, 30 Oct 2018 17:17:48 GMT

Dear Jobson

I'm going to work on this idea!

Thank you

Re: Change data in a column

jobsonkjoseph — Tue, 30 Oct 2018 17:20:50 GMT

Your welcome Nicolas.

Pls do share the final outcome.

Re: Change data in a column

MarcoWedel — Tue, 30 Oct 2018 23:06:37 GMT

Hi,

when loading your months as proper dual values you don't have to care about sorting issues as the default numerical search will be correct.

One way to do so would be to load

Month(Date#(month,'M')) as month

hope this helps

regards

Marco

Re: Change data in a column

Anonymous — Wed, 31 Oct 2018 12:00:41 GMT

Hello Marco, I did not understand how! I tried opening your Qlik file but I use the personal version.

Sorry, I am a beginner in Qlik and I have very limited knowledge

Regards

Nicolas

Re: Change data in a column

rubenmarin — Wed, 31 Oct 2018 12:11:48 GMT

Hi Nicolas, in the sorting tab you can use the Month field in number to sort the dimension. This can solve your issue using the mapping table.

Also, the use of Month() function to create the field can help, using that function months can be shown as text but sorted by num. Marco posts how you can use this function:

table:

LOAD

Month(Date#(month,'M')) as month

Note that having a dedicated field for month in number can help with some expression so it's not a bad idea to keep formats separated: one for selections other for internal expressions, but in most situations a unique month field created as a dual value can do the work.

Re: Change data in a column

Anonymous — Wed, 31 Oct 2018 12:45:38 GMT

Hello Rubin, I'll check your solution.

I'm running some other tests but then sent you some feedback.

Thank you for your help

greetings

Nicolas

Re: Change data in a column

Anonymous — Wed, 31 Oct 2018 15:57:02 GMT

Hi Jobson,

I found the solution to the problem, I used the Dual function, below

Mapping LOAD * Inline [

Num, Month

1, Jan,

2, Feb,

3, Mar,

4, Apr,

5, May,

6, Jun,

7, Jul,

8, Aug,

9, Sep,

10, Oct,

11, Nov,

12, Dec

];

TestTable:

LOAD

Dual (ApplyMap ('Map_MES', Per), Per) AS [Month]

From [archive]

regards

Nicolas