How to 'merge' two columns ?

[#1]

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

[#2]

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.

[Anonymous]

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

[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.

[Gysbert_Wassenaar]

See attached qvw.

[#6]

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

[#7 · Accepted solution]

If you need for each distinct XXX you could do something like (replace Your_Table with your table name):

NoConcatenate XXX_Tab:

Load

     Distinct(XXX) as XXX

Resident Your_Table;

Let X_Aux = NoOfRows('XXX_Tab');

For i = 1 to X_Aux

Let Y_Aux = Peek('XXX', $(i)-1, 'XXX_Tab');

Load

     $(Y_Aux) as XXX,

     Null() as State,

     date as timestamp,

Resident MasterCalendar;

Next i;

Drop Table XXX_Tab;

I think this should work. It will add for each XXX all the lines from MasterCalendar.

[#8]

Thank you Ionut !

This does exactly what I want

Have a good day