13 Replies Latest reply: Jul 5, 2011 11:01 AM by annie.lovelace RSS

    Date problem

      Hello everyone !

       

      I have two types of date : an enter date and an exit date ( DD-MM-YYYY ). I'd like to merge this two cells and have this display MM-YYYY.

       

      How can I do ??

       

      Thank you =)

        • Re: Date problem
          Stefan Wühl

          Hi Annie,

           

          second part of your question is easy: use date function to format the output:

           

          date ( expression [,format-code])

           

          e.g. in any expression

           

          =date( Date, 'MM-YYYY')

           

          {"Date" is a field with date format, you may need to parse your dates into correct format first, depending on your system settings}

           

          To receive your merged list, where are the two dates located, in separate tables?

          I would go for adding the merge to the load script. Could you post an excerpt of your current script?

           

          Regards,

          Stefan

            • Re: Date problem

              Here are my two tables :

               

              LOAD @1,

                        @2,

                        @3,

                        @4,

                        @5 as [Date_enter]

              FROM

              [...]

              (biff, no labels, table is In$);

               

               

              LOAD @1,

                        @2,

                        @3, 

                        @4,

                        @5 as [Date_exit]

              FROM

              [...]

              (biff, no labels, table is Out$);

                • Re: Date problem
                  Stefan Wühl

                  Hi Annie,

                   

                  it always depends on what you want to achieve wit your date.

                   

                  If you just want to merge the two dates into one field, and you don't need the other fields (@1 to @4),

                  I think you can concatenate the two tables:

                   

                  Date:

                  LOAD

                            @5 as Date

                  FROM

                  [...]

                  (biff, no labels, table is In$);

                   

                   

                  CONCATENATE (Date) LOAD 

                            @5 as Date

                  FROM

                  [...]

                  (biff, no labels, table is Out$);

                   

                  {If number of fields and fieldnames are identical, in fact you don't need the CONCATENATE, but I think this makes it more clear}

                   

                  Then use above expression to format the output.

                   

                  But I guess you want to preserve the original enter and exit date data.

                  Then you can use a separate table and reuse the already read data:

                   

                  EnterDate:

                  LOAD @1,

                            @2,

                            @3,

                            @4,

                            @5 as [Date_enter]

                  FROM

                  [...]

                  (biff, no labels, table is In$);

                   

                  ExitDate:

                  LOAD @1,

                            @2,

                            @3, 

                            @4,

                            @5 as [Date_exit]

                  FROM

                  [...]

                  (biff, no labels, table is Out$);

                   

                  Date:

                  Load

                  //[Date_enter],

                  [Date_enter] as Date

                  resident EnterDate;

                   

                  Concatenate (Date) Load

                  //[Date_exit],

                  [Date_exit] as Date

                  resident ExitDate;

                   

                  If you remove the comments from the lines, your Date data will be associated with your enter resp. exit dates (so if you select a date, the corressponding enter resp exit dates are also selected). If you leave the comments in, you get an not associated table (data island).

                   

                  Hope this helps,

                  Stefan

                  • Re: Date problem
                    Erich Shiino

                    If the @1, @2, etc mean the same thing in both tables it can be like this:

                    ( It will rename both fields to the same name and format them. You will need to use a flag to know if it is 'Enter' or 'Exit' event)

                     

                    Table:

                    LOAD @1, 'Enter' as Fact,

                              @2,

                              @3,

                              @4,

                              date(@5,'MM/YYYY') as [Date]

                    FROM

                    [...]

                    (biff, no labels, table is In$);

                     

                    CONCATENATE(Table) //Since all the fields are the same... the concanate is not actually necessary here

                    LOAD @1, 'Exit' as Fact

                              @2,

                              @3, 

                              @4,

                             date(@5,'MM/YYYY') as [Date]

                    FROM

                    [...]

                    (biff, no labels, table is Out$);

                     

                     

                    Hope this helps,

                     

                    Erich

                      • Re: Date problem

                        Everything you write helps me. But I have some people who enter at the date_enter and people who leave at date_exit. And I want to make 2 graph ( enter & exit ). When I choose a date (mm-yyyy), it shows me on the both graph the enters and exits.

                          • Re: Date problem
                            Stefan Wühl

                            Hi Annie,

                             

                            that should not be a problem.

                             

                            Either follow my last suggestion (uncomment lines), use a list box for date with my expression and a graph with =count(enter_date)

                            and

                            =count (exit_date)

                             

                            as expressions.

                             

                            Or use Erichs suggestions with a listbox for date and

                            Fact as dimension and count(Date) as expression.

                             

                            I think this should be enough.

                             

                            Regards,

                            Stefan

                              • Re: Date problem

                                Thank you ! It works. But how can I show just unique value instead of having several times the same date and in the graph only one point for the month (mm-yyyy) instead of several points for every days in the month (dd-mm-yyyy) ?

                                  • Re: Date problem
                                    Stefan Wühl

                                    Hi Annie,

                                     

                                    well, you should only have two bars in your graph in both cases if you followed my description (overall counts)...

                                     

                                    If you want the counts per month in your graph, you could add a calculated dimension in my case:

                                    =date(Date, 'MM-YYY')

                                     

                                    or in Erichs case add Date as dimension, fact as second dimension.

                                     

                                    If you post your example file here (there is an option in advanced editor to add files), I think we can work it out pretty fast.

                                     

                                    Cheers,

                                    Stefan

                                    • Re: Date problem

                                      Here is my qlikview example

                                        • Re: Date problem
                                          Stefan Wühl

                                          Hi Annie,

                                           

                                          the problem is that QlikView added a synthetic key because Name and Lastname are identical in tables Exit and Enter (you can see this in Table View CTRL-T).

                                           

                                          If you have a data scheme like this, I think Erichs approach is more practicable since it does not only merge the date field, but also the Names and Lastname and add a flag for the event.

                                           

                                          In the graph, either use two dimesions (first Date, then Fact) or if you want two graphs,

                                          use

                                          =sum(if (Fact='Enter',1))

                                          resp.

                                          =sum(if (Fact='Exit',1))

                                          as expression (and only Date as dimension).

                                           

                                          I don't have the xls Files, so I couldn't adapt your sample and try, but I think this should work.

                                           

                                          Regards,

                                          Stefan