7 Replies Latest reply: Jan 16, 2015 5:24 AM by Laura Sébille RSS

    How to 'merge' two columns ?

    Laura Sébille

      Hello everyone,

       

       

      Step 1:

      I have a table like this :

       

       

      XXX        state             timestamp

        A           clean     01/01/2015    02:07:00

        A           dirty       01/01/2015    02:55:00

        A           clean     01/01/2015    03:15:00

       

       

      And my goal is to be able de calculate for  one day for one hour (eg 02:00:00 to 03:00:00), how much time A was clean and how much time it was dirty.

       

      So I would like to insert some lines for the beginning of each hour:

       

      Step 2:

       

      XXX        state             timestamp

        A           clean     01/01/2015    02:07:00

        A           dirty       01/01/2015    02:55:00

        A            dirty      01/01:2015    03:00:00

        A           clean     01/01/2015    03:15:00

       

      And then I would be able to obtain :

       

      Step 3:

      XXX        state             timestamp                     time (minutes)

        A           clean     01/01/2015    02:07:00          43

        A           dirty       01/01/2015    02:55:00           5

        A           dirty      01/01:2015    03:00:00            15

        A           clean     01/01/2015    03:15:00           NA

       

      And then, when I filter on the date (I will create this variable from timestamp), I'll just have to sum the "time" and I will find the 60 minutes for one hour.

       

       

      I know how to move from step 2 to step 3  but I dont how to do step 2 which insert lines "01/01/2015 03:00:00", "01/01/2015  04:00:00"...

      I already have a calendar which contains those timestamp I need to add. but I don't know how to insert them in my table ?

       

      Can  you help me ?

       

      Please, do not hesitate to tell me if that is not clear.

       

       

      Thank you for your help !

       

      Have a good day

      Laura

        • Re: How to 'merge' two columns ?

          You can add them by concatenating your table with the resident calendar table and adding a value for XXX and state columns, like

          Null() as XXX; Null() as state;

          or like:

          'Generic' as XXX; 'Generic' as state;

          or whatever value you want to have it there.

          • Re: How to 'merge' two columns ?
            Rudolf Linder

            add an additional column

             

            60-minute(yourtimestamp) should give you the number in minutes before next full hour

             

            maybe the Format of timetstamp must be adjusted

            • Re: How to 'merge' two columns ?
              Colin Albert

              Hi Laura

               

              Have a look at this article http://community.qlik.com/docs/DOC-3786 by hic

               

              The item  "How do I propagate a value from above record to the current one?" is relevant to your question.

              • Re: How to 'merge' two columns ?
                Gysbert Wassenaar

                See attached qvw.

                • Re: How to 'merge' two columns ?
                  Laura Sébille

                  Hey everyone,

                   

                  Thank you for your answers. I've almost managed to do what I want. My example was not complete. I have several values for "XXX" so I need to add the "round hours"  (eg 02:00:00; 03:00:00). 

                   

                  Step 3:

                  XXX        state             timestamp                     time (minutes)

                    A           clean     01/01/2015    02:07:00          43

                    A           dirty       01/01/2015    02:55:00           5

                    A           dirty      01/01:2015    03:00:00            15

                    A           clean     01/01/2015    03:15:00           NA

                    B          dirty       01/01/2015     01:55:00          10

                    B          clean     01/01/2015      02:05:00         NA

                   

                  So far, I added the lines using :

                   

                  MasterCalendar:

                  Load Timestamp(recno()/24 + $(vMinDate)) as date

                  AutoGenerate (vMaxDate - vMinDate)*24;

                   

                  Load 

                  null() as XXX,

                  null() as State,

                  date as timestamp

                  resident MasterCalendar;


                  But I would need those new lines generated for each "XXX" value.  How can I do that ?


                  Thank you for your help

                  Laura