Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
DestinedTale
Contributor II
Contributor II

qlikview Load script covert dd/mm/yyyy hh:mm:ss to yyyymm

Hi,

I have searched through everywhere and tried all expression. But couldn't get the result.

I am working it in load script. There are also multiple values of the timestamp field. 

So i want to load the format from dd/mm/yyyy hh:mm:ss to yyyymm at load script. 

I need help to have an existing field example 29/04/2022 04:55:10 to 202204. 

Labels (3)
10 Replies
sergio0592
Specialist III
Specialist III

Hi,

Try with 

=date(FIELD,'YYYYMM')

 

if don't works try with

=date(date#(FIELD,'DD/MM/YYYY HH:MM:SS'),'YYYYMM')
DestinedTale
Contributor II
Contributor II
Author

Sorry both didn't work. Note that i am doing it at load script. So i remove the =. 

sergio0592
Specialist III
Specialist III

It should works in the load statement without the equal sign. 

DestinedTale
Contributor II
Contributor II
Author

Sorry, as mentioned i didn't include the = and it didn't work

sergio0592
Specialist III
Specialist III

Ok, so try without

DestinedTale
Contributor II
Contributor II
Author

ok. maybe im not being clear. I mean i try it without = already. And it didn't work. 

marcus_sommer
MVP & Luminary
MVP & Luminary

It might be done with something like:

load *, Year * 100 + Month as Period;
load *, year(Timestamp) as Year, num(month(Timestamp)) as Month;
load timestamp(timestamp#(Field, 'DD/MM/YYYY hh:mm:ss')) as Timestamp
from X;

Important is that the Field is numeric and if not that's converted to a numeric value whereby the applied format-pattern must be exactly defined.

- Marcus

DestinedTale
Contributor II
Contributor II
Author

i tried to load the year and month of my field 29/04/2022 04:55:10 at initial load

Then take year & month to form 202204 but it's not showing the data.

marcus_sommer
MVP & Luminary
MVP & Luminary

Before you could work with dates and times you need to ensure that they are interpreted as dates and times. If this didn't happens automatically because of the fact that the formatting of the values isn't identically to the interpretation-variables at the beginning of your script - you need to convert them manually with something like above mentioned: timestamp#(Field, 'DD/MM/YYYY hh:mm:ss')

The applied format-pattern must fit - for each char even spaces and any other special-chars.

- Marcus