Create tables from Existing tables

[Anonymous]

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!

[ahaahaaha]

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.

[dwforest]

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;