Pivoting date data in Diadem - summarising data by grouping

[Hotcold]

Hi,

Is there a way to summarise date data in Diadem?

For example, if I have a file with the following headings:

Date/time, on/off, duration

The Date/time column is recordrded in date, hours and seconds,

the on/off is either 1 or 0

duration is the difference in time values when on/off is 1.

Is there a way to summarise the date data by day or possibly by month? (The corresponding numbers in 'duration' would need to be assigned to the appropriate day or month)?

The equivalent calculation tool would be a pivot table in Excel.

Look foward to a response.

[Brad_Turpin]

Hi Hotcold,

Yes, we can do things like this in DIAdem.  How would you like to condense the values of the 2nd and 3rd columns?  Let's say you group by day, and in general there are 24 values per day in the 2nd and 3rd columns, now what?  Do you want to graph all 24 values as markers?  Do you want to add the 24 values together to display a total sum histogram with bars?  Do you want to average the 24 values, or find the min/max extrema of the 24 values?  How do you want to display this information in a report?

Would it be possible to send an example data set, so we can see what you have to work with?

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[Hotcold]

Hi Brad,

Thank you for your prompt response.

I have attached a file in the code section with some data. (Apologies for this, but the NI website said the content did not match the filetype). (I will try and get this right and send the attachement as an excel file. DIADEM should be able to read in the text file).

The columns are as follows: Event, Date,Time,Duration On,State.

(please note 1) that in the 'event' column there are 5 boilers - good for pivoting once grouped per boiler,

                     2) that 'State' is either a save or a bypass - also good for pivoting i.e. which days and boilers were in bypass, and which days and boilers were in save)

I would like to be able to summarise the data as follows:

Show a period(group by month or year) the sum of the Duration On, then display the result on a monthly histogram showing for example daily Duration On vs date.

Then

                   on an annual histogram showing for example monthly Duration On vs month.

As the last part of the exercise, how can I break out doing the 2 steps about for each of the 5 seperate boilers and show when each of the boilers were in a save or a bypass?

(Is a pivot table in excel required or can all the data processing be done in DIADEM?)

I appreciate your assistance in resolving this issue.

Sincerely

HotCold

[Brad_Turpin]

Hi HotCold,

I created a custom DataPlugin that reads your data file and exposes the adjacent 2 rows as one test (Start+Clear) that contains the info from both rows.  I also added a number of additional properties that could be inferred from the info you have in those rows.  Once all this is declared as properties to the DataFinder, you can query/pivot to your heart's content.  The big improvements of using DIAdem for this over Excel are twofold:

1)  You can query/pivot across multiple data files

2)  You have greatly enhanced reporting flexibility

Here's an example of a report I created along the lines of what you requested.  Please email me at brad.turpin@ni.com so I can help you get set up with this approach.

Brad Turpin

DIAdem Product Support Engineer

National Instruments