topic How to 'merge' two columns ? in QlikView

How to 'merge' two columns ?

Thu, 15 Jan 2015 12:37:09 GMT

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 ?

Thu, 15 Jan 2015 12:45:18 GMT

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 ?

Anonymous — Thu, 15 Jan 2015 12:56:37 GMT

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 — Thu, 15 Jan 2015 13:02:41 GMT

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 — Thu, 15 Jan 2015 13:49:16 GMT

See attached qvw.

Re: How to 'merge' two columns ?

Fri, 16 Jan 2015 09:17:44 GMT

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

Re: How to 'merge' two columns ?

Fri, 16 Jan 2015 09:54:09 GMT

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.

Re: How to 'merge' two columns ?

Fri, 16 Jan 2015 10:24:23 GMT

Thank you Ionut !

This does exactly what I want

Have a good day