Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Please can you tell me where I should use Date("Date") As FormattedDate?
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.
Are you using a crosstable load in your script?
Yes, but it has been unpivoted
What do you mean that it has been unpivoted? Can you share your script code?
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]
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;
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