Data Sorting in Ignition Report Table

Ignition
, ,
1

Dear Ignition family,

I am making one report to show consumption of power each week, I need to show week number as column and equipment as rows(more than 50 equipment). My Query returns data in following view
Query:
SELECT DATEPART(WEEK, t_stamp) as wk, max(Rinser_Recovery) as Rinser, max(CUB_Tank) as CUB, max(UGT) as UGT, max(CSD) as CSD, max(AQF) as AQF, max(wasa_ro) as WASA_RO, max(WWTP) as WWTP
from ICE_PAC group by DATEPART(WEEK, t_stamp) order by DATEPART(WEEK, t_stamp)

image

I need to show data in following view in Ignition Report(WK number in Column and equipment in rows):

image
image793×276 21.6 KB

Thanks!

2

You should be able to use a pivot in your query.

3

@JordanCClark thanks for reply, I have seen the example, its for two tables and sorry i missed to add some more information. Let me clarify more, in my table I need column name as equipment and max values to be show against equipment. where column name need to be week number converted from timestamp. I am able to sort my data wrt to week against each equipment in column like below pic
image
but i need data to be showed like below:
image

4

I should like to add one more thing with help of pivot, I was able to sort one column as row but in actual scenario I have more than 50, here was my query
–Pivot by WK number for one machine

SELECT ‘WASA_RO’ AS machine,
[37] as WK37, [36] as WK36
FROM
(
SELECT DATEPART(WEEK, t_stamp) as wk, max(WASA_RO) as mac from ICE_PAC group by DATEPART(WEEK, t_stamp)
) AS SourceTable
PIVOT
(
max(mac) for
wk IN ([37], [36])
) AS PivotTable;

and the answer

image

but i am looking to all add my columns except t_stamp under machines (as per snap above)

5

Try grouping and ordering by year as well as week.

6

@mcgheeiv thanks for your input can you please review my query above and suggest specifically what i should need to do to cover all my columns under machines column.

7

It might be easier to pull the data in with a simpler query

SELECT DATEPART(YEAR), DATEPART(WEEK), MAX(col_1), MAX(col_2), etc
FROM ICE_PAC
WHERE t_stamp >= date_beg and t_stamp <= date_end
GROUP BY YEAR, WEEK
ORDER BY YEAR, WEEK

Then use a python script to perform the pivot.

Pivots take 3 columns of data and turn them into a matrix with one column forming the matrix column headings (week), another being the row labels (machine), and another filling the cells according to the aggregation. Your columns should be YYYY-WW, machine name, value. Since your data is in 50 columns (WASA, CUB, AQF, etc.) a UNION could be used to create the SourceTable (syntax is not perfect):

SELECT 'WASA' as machine, CAST(DATEPART(YEAR, t_stamp) AS VARCHAR) + '-' + CAST(DATEPART(WEEK, t_stamp) AS VARCHAR)
    , MAX(WASA) AS v_float
UNION
SELECT 'CUB' as machine, CAST(DATEPART(YEAR, t_stamp) AS VARCHAR) + '-' + CAST(DATEPART(WEEK, t_stamp) AS VARCHAR)
    , MAX(CUB) AS v_float
UNION
SELECT 'AQF' as machine, CAST(DATEPART(YEAR, t_stamp) AS VARCHAR) + '-' + CAST(DATEPART(WEEK, t_stamp) AS VARCHAR)
    , MAX(AQF) AS v_float
WHERE t_stamp >= @date_beg and t_stamp <= @date_end
GROUP BY DATEPART(YEAR, t_stamp), DATEPART(WEEK, t_stamp)
ORDER BY DATEPART(YEAR, t_stamp), DATEPART(WEEK, t_stamp)
ETC, ETC, ETC (for all 50 machines)

Then all you need is to create the YEAR and WEEK range for use in the outer SELECT and the IN clause.

8

If the database table can be changed, a table with the following 3 columns would make the pivot solution easier:

  • machine_name VARCHAR(50)
  • t_stamp DATETIME
  • machine_value FLOAT
SELECT * FROM
(
	SELECT machine_name, function_of_year_week(t_stamp) AS year_week, MAX(machine_value) AS machine_value 
	FROM ICE_PAC
	WHERE t_stamp >= '{Root Container.date_beg}' AND t_stamp <= '{Root Container.date_end}'
	GROUP BY xxx
	ORDER BY xxx
) t
PIVOT(
	MAX(machine_value)
	FOR year_week IN ({Root Container.dates_pivot})
) as P
9 
sampleHeaders = ['wk', 'WASA', 'CUB', 'AQF']
sampleData = [
              [36, 5.168243E7, 4.598042E07, 8.83453E7],
              [37, 44544, 47360, 82432]
             ]
             
dataIn = system.dataset.toDataSet(sampleHeaders, sampleData)

colNamesIn = list(dataIn.getColumnNames())

# Initialize new headers and data
headers = ['Equipment'] + dataIn.getColumnAsList(colNamesIn.index('wk'))
data = []
# Remove the pivot column name for easier iteration of the dataset.
colNamesIn.remove('wk')

# Iterate through the columns
for col in colNamesIn:
	newRow = [col]
	# Iterate throu the rows
	for row in xrange(dataIn.rowCount):
		newRow.append(dataIn.getValueAt(row, col))
	data.append(newRow)

dataOut = system.dataset.toDataSet(headers, data)

         Sample Dataset

row | wk | WASA       | CUB        | AQF       
-----------------------------------------------
0   | 36 | 51682430.0 | 45980420.0 | 88345300.0
1   | 37 | 44544.0    | 47360.0    | 82432.0   

          Resultant Dataset

row | Equipment | 36         | 37     
--------------------------------------
0   | WASA      | 51682430.0 | 44544.0
1   | CUB       | 45980420.0 | 47360.0
2   | AQF       | 88345300.0 | 82432.0
1 Like
10

Thanks @JordanCClark @mcgheeiv for helping me alot. I am pretty new with scripting but for sure will read and try script made by @JordanCClark. I have tried query method with below queries and found results.
SELECT ‘CAN1’ as machine, DATEPART(WEEK, t_stamp) as wk, DATEPART(YEAR, t_stamp) as yr
, MAX(CAN1_INC) AS Reading, (select max(CAN1_INC)-min(CAN1_INC) from CANGroup where DATEPART(WEEK, t_stamp)=37) as Cons from CANGroup
where DATEPART(WEEK, t_stamp)=37
GROUP BY DATEPART(YEAR, t_stamp), DATEPART(WEEK, t_stamp)

UNION
SELECT ‘CAN2’ as machine, DATEPART(WEEK, t_stamp) as wk, DATEPART(YEAR, t_stamp) as yr
, MAX(CAN2_INC) AS Reading, (select max(CAN2_INC)-min(CAN2_INC) from CANGroup where DATEPART(WEEK, t_stamp)=37) as Cons from CANGroup
where DATEPART(WEEK, t_stamp)=37
GROUP BY DATEPART(YEAR, t_stamp), DATEPART(WEEK, t_stamp)
UNION
SELECT ‘CAN3’ as machine, DATEPART(WEEK, t_stamp) as wk, DATEPART(YEAR, t_stamp) as yr
, MAX(CAN3_INC) AS Reading, (select max(CAN3_INC)-min(CAN3_INC) from CANGroup where DATEPART(WEEK, t_stamp)=37) as Cons from CANGroup
where DATEPART(WEEK, t_stamp)=37
GROUP BY DATEPART(YEAR, t_stamp), DATEPART(WEEK, t_stamp)
UNION
SELECT ‘Glass’ as machine, DATEPART(WEEK, t_stamp) as wk, DATEPART(YEAR, t_stamp) as yr
, MAX(Glass_INC) AS Reading, (select max(Glass_INC)-min(Glass_INC) from CANGroup where DATEPART(WEEK, t_stamp)=37) as Cons from CANGroup
where DATEPART(WEEK, t_stamp)=37
GROUP BY DATEPART(YEAR, t_stamp), DATEPART(WEEK, t_stamp)
Below was the result, I have passed week number and year parameters by Simple query and can see my table in Report. thanks again @JordanCClark and @mcgheeiv

image