topic Distribute grouped values for periods of daily values in QlikView

Distribute grouped values for periods of daily values

Fri, 03 Feb 2017 09:17:21 GMT

Hello everyone, I need your help to spread the values that are part of a range of dates, to daily values.
Enclosed is an example of what I'd need.
my data source is a single file QVD with the fields as in the Excel.
To consider that my total assets are about 450.
Thanks to those who can help me

Re: Distribute grouped values for periods of daily values

sunny_talwar — Fri, 03 Feb 2017 09:36:17 GMT

These belong to different id... the final output will only have the three columns you have specified?? or those will repeat just like repeating value??

Re: Distribute grouped values for periods of daily values

Fri, 03 Feb 2017 09:41:54 GMT

I need a new QVD with three fields.

Re: Distribute grouped values for periods of daily values

sunny_talwar — Fri, 03 Feb 2017 09:59:13 GMT

Try this

Table:

LOAD asset,

date,

avg

FROM

[..\..\..\Downloads\targhe1.xlsx]

(ooxml, embedded labels, table is Foglio1);

tmpTable:

LOAD *,

Date(If(asset = previous(asset), Peek('date') + 1, date)) as startdate

Resident Table;

FinalTable:

LOAD Date(startdate + IterNo() - 1) as date,

avg as value,

asset

Resident tmpTable

While startdate + IterNo()-1 <= date;

DROP Tables Table, tmpTable;

Re: Distribute grouped values for periods of daily values

Fri, 03 Feb 2017 10:16:17 GMT

the final work is definitely what you need me. Unfortunately I was a bit inaccurate to ask the question because the avg field is not present in the original QVD but should first be averaged and then should apply your current processing

Re: Distribute grouped values for periods of daily values

sunny_talwar — Fri, 03 Feb 2017 10:17:30 GMT

What are we averaging? How does the raw data look like?

Re: Distribute grouped values for periods of daily values

Fri, 03 Feb 2017 10:28:33 GMT

- To group together assets;
- Ordered by date;
- The average (avg) is given from the delta area, divided by the difference in days between the date referred to by the value and the previous date.

id	asset	location	metername	delta	date	avg	mode calculating avg
292085	AAAAAAA	AUTOPARCO	CHILOMETRI	0	16/01/2014	null	delta (0) / null _________ (16/01/2014 - null)
293766	AAAAAAA	AUTOPARCO	CHILOMETRI	235	19/02/2014	6.911765	delta (235) / 34 ___________(19/02/2014 - 16/01/2014)
294590	AAAAAAA	AUTOPARCO	CHILOMETRI	284	07/03/2014	17.75	delta (284) /16________ (07/03/2014 - 19/02/2014)
294669	AAAAAAA	AUTOPARCO	CHILOMETRI	51	10/03/2014	17	delta (51) /3_______ (10/03/2014 - 07/03/2014)

Re: Distribute grouped values for periods of daily values

sunny_talwar — Fri, 03 Feb 2017 10:34:19 GMT

Try this:

Table:

LOAD asset,

date,

delta

FROM

[..\..\..\Downloads\targhe1.xlsx]

(ooxml, embedded labels, table is Foglio1);

tmpTable:

LOAD *,

Date(If(asset = previous(asset), Peek('date') + 1, date)) as startdate

Resident Table;

FinalTable:

LOAD Date(startdate + IterNo() - 1) as date,

delta/(date - startdate + 1) as value,

asset

Resident tmpTable

While startdate + IterNo()-1 <= date;

DROP Tables Table, tmpTable;

Re: Distribute grouped values for periods of daily values

Fri, 03 Feb 2017 12:17:00 GMT

I take advantage of your kindness ....
The drawing is fine if the asset field I only have a unique value, but I have problems when loading a source (as attached) with many different assets. Or rather, if I high daily values do not coincide with grouped delta.

In your opinion, you can delete the lines that have to be calculating delta zero?

Re: Distribute grouped values for periods of daily values

sunny_talwar — Fri, 03 Feb 2017 12:21:05 GMT

Not sure I understand this:

In your opinion, you can delete the lines that have to be calculating delta zero?

Can you check if this works?

Table:

LOAD asset,

Date(Floor(data)) as date,

delta

FROM

[targhe2.xlsx]

(ooxml, embedded labels, table is Sheet1);

tmpTable:

LOAD *,

Date(If(asset = previous(asset), Peek('date') + 1, date)) as startdate

Resident Table

Order By asset, date;

FinalTable:

LOAD Date(startdate + IterNo() - 1) as date,

delta/(date - startdate + 1) as value,

asset

Resident tmpTable

While startdate + IterNo()-1 <= date;

DROP Tables Table, tmpTable;

Re: Distribute grouped values for periods of daily values

Fri, 03 Feb 2017 12:58:21 GMT

I solved by adding a where clause after the first load:

Table:

LOAD asset,

Date(Floor(data)) as date,

delta

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE(delta <> '0');

in this way the calculation is performed without considering the deltas equal to zero.
It remains a small problem for the first readings of each asset which are also zero but should be considered (min (dates) group by assets)

Re: Distribute grouped values for periods of daily values

sunny_talwar — Fri, 03 Feb 2017 13:15:37 GMT

What is the issue?

It remains a small problem for the first readings of each asset which are also zero but should be considered (min (dates) group by assets)

Re: Distribute grouped values for periods of daily values

sunny_talwar — Fri, 03 Feb 2017 13:32:15 GMT

Try this:

Table:

LOAD asset,

Date(Floor(data)) as date,

delta

FROM

[targhe2.xlsx]

(ooxml, embedded labels, table is Sheet1);

tmpTable:

LOAD *

Where Not(delta = 0 and Flag > 1);

LOAD *,

AutoNumber(RecNo(), asset) as Flag,

Date(If(asset = previous(asset), Peek('date') + 1, date)) as startdate

Resident Table

Order By asset, date;

FinalTable:

LOAD Date(startdate + IterNo() - 1) as date,

delta/(date - startdate + 1) as value,

asset

Resident tmpTable

While startdate + IterNo()-1 <= date;

DROP Tables Table, tmpTable;

Re: Distribute grouped values for periods of daily values

Fri, 03 Feb 2017 15:04:27 GMT

you were really nice and helpful.

Sorry if I was not very precise in directions

Re: Distribute grouped values for periods of daily values

sunny_talwar — Fri, 03 Feb 2017 15:05:57 GMT

No worries