Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
crichter14
Creator
Creator

Date Conversion in Load Script - Qlik Sense

I copied this from another post because I have the same issue in Qlik Sense and the solution is not working.  Except for the format I need is dd-mmm-yyyy.

"I've a problem with a data format.
What I need: to change format in loading script.
Actual format: numeric, i.e.:('42079')
Need to change in: 'DD-MMM-YYYY';

In the beginning of loading script I've already set this: SET DateFormat='DD-MMM-YYYY'; but is not working on that specific field.
I tried to use lastly (cause I made some testing before posting):
Date(fieldname,'DD/MM/YYYY') as Data,   (fieldname is field name from original loading file)"

Suggestions?
Thank you!

Labels (4)
3 Replies
Or
MVP
MVP

I'm a tad confused. If you want DD-MMM-YYYY, why are you using Date(fieldname,'DD/MM/YYYY') rather than Date(fieldname,'DD-MMM-YYYY')?

Or_0-1627413735466.png

 

 

crichter14
Creator
Creator
Author

Sorry that was a copy paste on my part.  Final should be dd-mmm-yyyy (the issue with copying someone else's post).  I can't seem to figure out if the QVD this is pulling from is saving the fields as number or a text.   This is the QVD output.  

All of these should be like the 27-Jul-21, but they should be 27-JUL-2021.

 

Example:

EXPECTED DELIVERY DATEEXPECTED INVOICE DATEEARLIEST CAN PULL ON PICKS
27-Jul-214440544440
444544445544454
445644456544538
445464454744517
444474444844426
Or
MVP
MVP

That's a weird example, because one value is formatted as a date and all the others are numeric. Is that actually the case for your values?

If you want the month names to be UPPERCASE, you can set your own values in the MonthNames variable. The default, at least for me, is Proper case.

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

If your values are actually like this where it's a case of mixed numeric and string (date), you'll probably need an if() statement. Something along the lines of:

=if(isnum([EXPECTED DELIVERY DATE]),
date([EXPECTED DELIVERY DATE],'DD-MMM-YYYY'),
date(date#([EXPECTED DELIVERY DATE],'DD-MMM-YYYY'),'DD-MMM-YYYY'))