Coverting Date from DD/MM/YYYY to MM/YYYY

[#1]

Hello,

I have a table in which one of the fields is named date. When I uploaded this to Qlik the dates read:

42736

42767

42795

...

I'm unsure what format this is but I am able to convert this into DD/MM/YYYY using the expression Date(num#(Date,'#')).

This successfully changes the dates and they then read:

01/01/2017

01/02/2017

01/03/2017

But I want to have the dates read just 01/2017, 02/2017...

Is it possible to make the date read in that way? If so, please let me know.

Thanks in advance,

Alison

[vinod2086]

Hi,

Try This

Your monthyear field is in string format. Make it in Number format like below

date(date#(MonthYear,'YYYY-MMM'),'YYYY-MMM') as MonthYear

Now go to Sort tab and sort it by Numerical values.

[#3]

I don't know if I'm being incredibly foolish but I have been unable to get any of these to work.

I have solved the problem by going data load editor > auto-generated section and then doing this

[42736] as '01/2017',

[42767] as '02/2017',

[42795] as '03/2017'

This isn't really satisfactory as it is time consuming.

Please can you be more explicit about where and when I should be using the various expression you've mentioned as I am very much a beginner.

Thanks,

Alison

[#4]

Please can you tell me where I should use Date("Date") As FormattedDate?

[#5]

I had tried this but it hasn't worked for me. When try to change the field type in the table view of the data manager the field values read - and are in dark grey.

[sunny_talwar]

Are you using a crosstable load in your script?

[#7]

Yes, but it has been unpivoted 

[sunny_talwar]

What do you mean that it has been unpivoted? Can you share your script code?

[#9]

I mean that I transposed a number of fields into rows. The original excel sheet was in crosstab format, but I changed that at the data manager.

This is in the auto-generated section, if that's what you wanted.

[Income]:

LOAD [Income],

[42736],

[42767],

[42795]

[sunny_talwar · Accepted solution]

So I am guessing you are doing something like this... are you?

Income:

CrossTable (MonthYear, Data)

LOAD Income,

     [42736],

     [42767],

     [42795]

FROM .....;

Now you need to do this

Income_Final:

LOAD Income,

     Date(MonthStart(Num#(MonthYear)), 'MM/YYYY') as MonthYear

     Data

Resident Income;

DROP Table Income;

[#11]

Sorry, I made an error. This is how it reads after it has been unpiovted:

[Income] AS [Income Source],

[42736],

[42767],

[42795]

I don't know if that changes anything