Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to 'merge' two columns ?

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

1 Solution

Accepted Solutions
Not applicable

Re: How to 'merge' two columns ?

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.

7 Replies
Not applicable

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.

hrlinder
Not applicable

Re: How to 'merge' two columns ?

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
Not applicable

Re: How to 'merge' two columns ?

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
Not applicable

Re: How to 'merge' two columns ?

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: How to 'merge' two columns ?

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

Not applicable

Re: How to 'merge' two columns ?

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.

Not applicable

Re: How to 'merge' two columns ?

Thank you Ionut !

This does exactly what I want

Have a good day