extracting date field in Qlikview

[Anonymous]

Hi there,

I have a certain field in Excel (DEP departure time), which I would like to extract in to 4 fields: "Year", "Month", "Day", and "Time"

I have used the folowing code:

LOAD

    DEP,

    Year (DEP) as DepYear,

     Month (DEP) as DepMonth,

     Day (DEP) as DepDay,

     Time (DEP) as DepTime

From ....

Qlikview loads the new field without any problems, however when I add these fields in my sheet they contain no value.

What could cause this to happen?

Thanks in advance!!

iSam

[#2]

The cause could be that it don't recognize your DEP as a timestamp. Try using the date# function. I.e

LOAD

DEP,

Year(date#(DEP,'dd-mm-yyyy hh:mm')) as myYear,

....

and so on.

/Michael

[#3 · Accepted solution]

Hi Sam

There may be a more elegant way to achieve this, but you can do it by breaking the DEP field into two sub-fields - date and time.  Try this:

LOAD                DEP as DEP,

               Year(Subfield(DEP, ' ', 1)) as DepYear,

                        Month (Subfield(DEP, ' ', 1)) as DepMonth,

               Day (Subfield(DEP, ' ', 1)) as DepDay,

               Time (Subfield(DEP, ' ', 2)) as DepTime,

               [ARR/NFD],

               [CCD/DRB],

               POD

FROM      data II.csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Good luck

Trevor

[Anonymous]

Hi Trevor,

Thanks for your help! It Now works!

I've added two extra lines:

Date (SubField(DEP, ' ',1)) as DepDate, (Departure Date)

DepDate & DepTime as Departure, (doenst work)

and

Pod as Pod,

Year(Subfield(POD, ' ', 1)) as PodYear,

Month (Subfield(POD, ' ', 1)) as PodMonth,

Day (Subfield(POD, ' ', 1)) as PodDay,

Time (Subfield(POD, ' ', 2)) as PodTime 

If I want to calculate the lead time between the departure and point of delivery what formula should I use in my expression? I tried: Min(DepTime) - (PodTime). But that doens't work either

Hope u can help me.

[#5]

Hi, glad it helped.

I'm off to work right now,but if you send me the model I'll work it out for you tonight.

Description: Neon Signature