4 Replies Latest reply: Jul 21, 2011 6:18 PM by trevordoble RSS

    extracting date field in Qlikview

    Aissam Boumejjane

      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:




          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!!



        • extracting date field in Qlikview

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





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



          and so on.



          • extracting date field in Qlikview

            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,




            FROM      data II.csv

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


            Good luck


              • extracting date field in Qlikview
                Aissam Boumejjane

                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)



                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.