# Convert ordinary dataset to grouped dataset **URL:** \ **Category:** Ignition\ **Tags:** ignition79, vision, scripting\ **Created:** [March 6, 2021, 5:15pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185 "2021-03-06T17:15:15Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [March 6, 2021, 5:15pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/1 "2021-03-06T17:15:15Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/4/0415e12070ab1d0c7fe22ca37dec0a3c960edc9d.png) I can’t think of any other way (the RAM inside my head is old…) to do this…? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 6, 2021, 5:36pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/2 "2021-03-06T17:36:57Z")
That is called a PIVOT. Your task is easily accomplished by the [view()](https://www.automation-pros.com/simaids/doc/expression.view.html) function from my (free!) [Simulation Aids](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-simulation-aids) module. You would use an expression binding on `data` on table #2 like so: ```auto view("Select Date Pivot Pieces For 'Shift '+str(Shift) Order By Date", {Root Container.Table 1.data}) ``` ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [March 6, 2021, 5:56pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/3 "2021-03-06T17:56:26Z")
Well, thank you @pturmel. I tried and this is what I get… ![Screenshot_2](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/2/f28e699953c1db6cd1530cc7de06ec463e5987d3.png) and this is in my expression binding on data on table #2: ```auto 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 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 6, 2021, 6:01pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/4 "2021-03-06T18:01:25Z")
Ah, this is both a pivot and a grouping operation. Try this: ```auto 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: ```auto view("Select datum Group By datum Pivot sum(stevilo_kosov) For 'Shift '+str(izmena) Order By datum", {Root Container.Table.data}) ``` ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [March 6, 2021, 6:15pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/5 "2021-03-06T18:15:33Z")
Cool. This is what I get now (with the sum Pieces): ![Screenshot_3](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/2/a28c458502dd165cb25b65b3bf80b969ed006498.png) 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… ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 6, 2021, 6:29pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/6 "2021-03-06T18:29:40Z")
No input for a combination yields a null output. ~~You could explicitly include a zero in the sum expression, like so:~~ ```auto 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. ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [March 6, 2021, 6:42pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/7 "2021-03-06T18:42:56Z")
I really appreciate your help. 👍 But it’s the same… ![Screenshot_4](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/4/f41ee051f6403ff04ff01b39182cdd5e50c8e6d8.png) No zeroes for Shift2 and 3… ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 6, 2021, 6:46pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/8 "2021-03-06T18:46:46Z")
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.) ---
**Author:** ![zxcslo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zxcslo/32/61610_2.png) [@zxcslo](https://forum.inductiveautomation.com/u/zxcslo)\ **Post date:** [March 8, 2021, 9:21am UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/9 "2021-03-08T09:21:06Z")
Thank you very much for your help. 👍 I ended up with post-processing as you said to replace NULL with zeros. Works great. ---
**Author:** ![jesusaltamirano600](https://avatars.discourse-cdn.com/v4/letter/j/b782af/32.png) [@jesusaltamirano600](https://forum.inductiveautomation.com/u/jesusaltamirano600)\ **Post date:** [December 15, 2021, 1:14am UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/10 "2021-12-15T01:14:06Z")
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. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [December 15, 2021, 8:00am UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/11 "2021-12-15T08:00:22Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 15, 2021, 12:30pm UTC](https://forum.inductiveautomation.com/t/convert-ordinary-dataset-to-grouped-dataset/44185/12 "2021-12-15T12:30:36Z")
> [@jesusaltamirano600](#): > > I’m not allowed to install any module in the ignition gateway. 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.