Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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.
See attached qvw.
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
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.
Thank you Ionut !
This does exactly what I want
Have a good day