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:

       

      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

        • 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

           

          LOAD

           

          DEP,

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

          ....

           

          and so on.

           

          /Michael

          • 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,

                           [ARR/NFD],

                           [CCD/DRB],

                           POD

            FROM      data II.csv

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

             

            Good luck

            Trevor

              • 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)

                 

                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.