Convert ordinary dataset to grouped dataset

Ignition
, ,
1

Well, I apologize if the topic title is not correct, but English is not my native language.
I’m asking for help to convert the dataset from the table1 to another dataset (table2) that can be used in the bar chart (I think it’s called ‘Grouped’ dataset).
And that should be done in the python (Jython) script in the Data property change event of the table1.

From_table1_to_table2
From_table1_to_table21409×934 35.9 KB

I can’t think of any other way (the RAM inside my head is old…) to do this…?

2

That is called a PIVOT. Your task is easily accomplished by the view() function from my (free!) Simulation Aids module. You would use an expression binding on data on table #2 like so:

view("Select Date Pivot Pieces For 'Shift '+str(Shift) Order By Date",
  {Root Container.Table 1.data})
3

Well, thank you @pturmel.
I tried and this is what I get…

Screenshot_2
Screenshot_21409×934 35.5 KB

and this is in my expression binding on data on table #2:

view("Select datum Pivot stevilo_kosov For 'Shift '+str(izmena) Order By datum",{Root Container.Table.data})

The field names on the pictures are translated into English.
The real (in my language) are:
Date = datum
Pieces = stevilo_kosov
Shift = izmena

4

Ah, this is both a pivot and a grouping operation. Try this:

view("Select datum Group By datum Pivot stevilo_kosov For 'Shift '+str(izmena) Order By datum",
  {Root Container.Table.data})

You may need to sum Pieces in the pivot, like so:

view("Select datum Group By datum Pivot sum(stevilo_kosov) For 'Shift '+str(izmena) Order By datum",
  {Root Container.Table.data})
1 Like
5

Cool. This is what I get now (with the sum Pieces):

Screenshot_3
Screenshot_31409×1243 40.4 KB

If I try to bind now the Table2 data property to the Barchart, there are warnings…
because in the last row for shift2 and 3 there are no values…

6

No input for a combination yields a null output. You could explicitly include a zero in the sum expression, like so:

view("Select datum Group By datum Pivot sum(stevilo_kosov+[0]) For 'Shift '+str(izmena) Order By datum",
  {Root Container.Table.data})

{ Edit: the above doesn’t work because the sum doesn’t even execute for missing group/pivot combinations. }

Inside the Pivot, stevilo_kosov is a python list. If you turn on the debugger mentioned in the doc, your diagnostic console will get a copy of the jython written by the view() function to solve your problem.

7

I really appreciate your help. :+1:
But it’s the same…

Screenshot_4
Screenshot_41409×1161 82.3 KB

No zeroes for Shift2 and 3…

8

Hmm. Looks like a post-process would be needed to convert nulls to zeros. ):

I’d use objectScript() for that, naturally. (A better runScript than runScript. Also from Simulation Aids.)

1 Like
9

Thank you very much for your help. :+1:
I ended up with post-processing as you said to replace NULL with zeros. Works great.

1 Like
10

Hello,

Is it possible to do the same without using the module? I’m not allowed to install any module in the ignition gateway.

Thanks in advance.

11

Could you describe exactly what you need to achieve ? Maybe give an example input and output.
A basic pivot is relatively easy to do, but depending on the use case there might be little twists to add to the process.

12

You should ask permission. I create modules for functions/features that are difficult or impossible to do otherwise. I made Simulation Aids free to make its use as easy as possible, by anyone. I install it on every gateway I work on. That it won't load on Edge is one of the reasons I don't use Edge.