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

[DestinedTale]

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. 

[sergio0592]

Hi,

Try with 

=date(FIELD,'YYYYMM')

if don't works try with

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

[DestinedTale]

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

[sergio0592]

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

[DestinedTale]

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

[sergio0592]

Ok, so try without

[DestinedTale]

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

[marcus_sommer]

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]

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]

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