Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a question which can be very simple to many experts here. Can you please help me with taking daily data from one base QVD and do last 'n' number of days sum and store in a new QVD table.
Here is an example.
dailytable:
Date Count 1 Count 2 Count 3
01-01-2017 30 40 60
01-02-2017 10 10 70
01-03-2017 40 50 30
01-04-2017 60 20 20
01-05-2017 20 20 40
01-06-2017 60 50 30
01-07-2017 20 60 40
01-08-2017 60 20 20
01-09-2017 10 50 10
01-10-2017 60 30 10
01-11-2017 20 20 30
I want to create new 7 days and cumulative tables from basetable by adding last 7 days data with out using RangeSum.
weeklysumtable:
Date 7Count 1 7Count 2 7Count 3
01-01-2017 30 40 60
01-02-2017 40 50 130
01-03-2017 80 100 160
01-04-2017 140 120 180
01-05-2017 160 140 220
01-06-2017 220 190 250
01-07-2017 240 250 290 (one week added 1-7 data from daily table)
01-08-2017 270 230 250 (one week added 2-8 data from daily table)
01-09-2017 270 270 190 (one week added 3-9 data from daily table)
01-10-2017 290 250 170 (one week added 4-10 data from daily table)
01-11-2017 250 250 180 (one week added 5-11 data from daily table)
Like this add up only last seven days and display the data on every day. How can I do it from loading a QVD file to Save it in new QVD. Please guide me. Thanks for the help!
Hi,
In QVD file you can upload anybody or all fields, without deleting them in the last line of the above code (see attached file).
In diagrams in UI, you can also use any saved fields. If the diagram does not depend on the user's choice, simply use Set Analysis expression, for example {1} in it.
Ok, its a bit cumbersome, but this does it (i think). It includes the current day in the 7 day total, you can adjust by changing what's included in the RangeSum.
daily_temp:
LOAD *
INLINE [
Date, Click_event, Success_event, Failure_event
'01-01-2017', 30, 40, 60
'01-02-2017', 10, 10, 70
'01-03-2017', 40, 50, 30
'01-04-2017', 60, 20, 20
'01-05-2017', 20, 20, 40
'01-06-2017', 60, 50, 30
'01-07-2017', 20, 60, 40
'01-08-2017', 60, 20, 20
'01-09-2017', 10, 50, 10
'01-10-2017', 60, 30, 10
'01-11-2017', 20, 20, 30
];
daily:
NoConcatenate
LOAD *,
RangeSum(Success_event,Peek(Success_event,-1),Peek(Success_event,-2),Peek(Success_event,-3),Peek(Success_event,-4),Peek(Success_event,-5),Peek(Success_event,-6)) as SevenDay_Success
Resident daily_temp;
drop table daily_temp;