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
Not sure what you mean by unpivoting? Are you using The Crosstable Load?
No no, it has worked. Thank you so much!
Awesome
Hi Sunny,
Would it be possible for you to explain this script to me, I am having difficulty understanding it and so would greatly appreciate it.
Thank you in advance,
Alison
What exactly are you having difficulty understanding? If you can point it out to me, I will probably be able to help you better
I don't understand it in its entirety in all honesty. I don't understand how that commands the date to be displayed as DD/YYYY
In the first part I am converting an Pivoted data stored in an Excel into a un pivoted data into QlikView using The Crosstable Load. If you did not get this, then open the link to read more about it.
Income:
CrossTable (MonthYear, Data)
LOAD Income,
[42736],
[42767],
[42795]
FROM .....;
Next, we have the problem that date is read as a number. In fact, the problem is that CrossTable reads all the fields as text. So, 42736, 42767, 42795 are not read as numbers, but text. In order to convert it into number, I have used Num#() function. Now once it is a number, all I needed was a date function to show it as a date, but just to make sure that they are also month start dates, I used MonthStart() function between date() and Num$() functions.
Income_Final:
LOAD Income,
Date(MonthStart(Num#(MonthYear)), 'MM/YYYY') as MonthYear
Data
Resident Income;
DROP Table Income;
Does it help now?
This is very helpful, thank you Sunny