Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Sir/Madam
Kindly advise how to convert report date YYYYMMDD to following formats in scrip :
1. MMM-YY
2. YYYY
3. MM
4. DD
Thank you
Tracy
Ah, that's a common weirdness in QlikView. And actually it isn't weird at all. All fields in QlikView are of type dual, meaning that they have both a numerical value and a string representation (or format). The MonthYear values in your data model are in reality dates with a day component. The first one is Oct 12, 2013, the second one is Oct 18, 2013. The string formatting that you use in your load script doesn't change the numerical value, but will assign a MMM-YY format string to that field.
The field values though remain different, while their representation looks the same. All objects in QlikView will list/order dimensions first on their numerical value.
Simple to solve: change the load script for MonthYear into something like:
LOAD Date,
Date(MonthStart(Date), 'MMM-YY') as MonthYear,
:
and gone is your issue.
Best,
Peter