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 remove already accumulated values from data source?

Hi,

We have recently connected to a new data source and when extracting data we have noticed the values have been accumulated by day.

Within each day there will be multiple 'areas' which we would like to show the individual transactions by each time stamp.

The excel file attached shows a cut down version of what the extract looks like and also how we would like to show it.

Any support would be hugely appreciated!

Charlie

Tags (1)
6 Replies
Not applicable

Re: How to remove already accumulated values from data source?

can you please explain what the original data is summing on and how you would like it to be sumed?

it looks like the only difference is the value in Sum field

Not applicable

Re: How to remove already accumulated values from data source?

The accumulation occurs within the data source and I will not be able to interrogate into this system further to get to the raw counts.

The Value is the only field I would like to adjust.. I would like to remove the accumulated values and replace with the individual counts by time group?

Sorry if I am not explaining well.

Charlie

Not applicable

Re: How to remove already accumulated values from data source?

Below is an example of how the data is shown in QV, and below is how we would like to present.

Thanks

Capture.PNG

Not applicable

Re: How to remove already accumulated values from data source?

what do you consider to be a time group? are the groups the actual date/time combiantion and you want to know how much occured within each combination isntead of it being cumulative?

   

1/1/2015#######01/01/2015 11:00:005
1/1/2015#######01/01/2015 12:00:002

in the snippet above, do you want to accumulate to say there are 5 transaction on 1/1/2015 within the 11:00 hour and there were 2 transactions on 1/1/2015 in the 12:00 hour?

MVP
MVP

Re: How to remove already accumulated values from data source?

a:

LOAD Date,

     Time,

     TimeStamp,

     Sum,

     Area

FROM

Example.xls

(biff, embedded labels, header is 1 lines, table is Sheet1$);

b:

LOAD Date,

     Time,

     TimeStamp,

     Sum,

     Area,

     if(Area=peek(Area) and Date=Peek(Date), Sum - Peek(Sum), Sum) as NewSum

Resident a

order by Area, TimeStamp;

DROP Table a;

I think the Sum (not cumulated) in row 45 of excel should be 2

Not applicable

Re: How to remove already accumulated values from data source?

you should be able to use the time and area as a dimension and count the number of transactions

Community Browser