Dynamic number of columns not displaying properly

mac173173

You can see from the image below that my rows are showing the first column data repeated for each column with data. I would like to have the first column, Writers and Authors, shown once with the other columns stacked behind it. I have about 110 rows, each with a different number of columns.
Please understand that I am not a coder and am manipulating what was built for me. No budget to hire a coder, so any help is greatly appreciated. The code on the site is below.

Snip of JSON

{
    "data":[
  ["11-1011","Chief Executives",44.0401,"Public Administration." ],
  ["11-1011","Chief Executives",52.0101,"Business/Commerce, General." ],
  ["11-1011","Chief Executives",52.0201,"Business Administration and Management, General." ],
  ["11-1011","Chief Executives",52.0206,"Non-Profit/Public/Organizational Management." ],
  ["11-1011","Chief Executives",52.0701,"Entrepreneurship/Entrepreneurial Studies." ],
  ["11-1011","Chief Executives",52.0704,"Social Entrepreneurship." ],
  ["11-1011","Chief Executives",52.0801,"Finance, General." ],
  ["11-1011","Chief Executives",52.1101,"International Business/Trade/Commerce." ],
  ["11-1011","Chief Executives",52.1301,"Management Science." ],
  ["11-1021","General and Operations Managers",1.8202,"Veterinary Office Management/Administration." ],
  ["11-1021","General and Operations Managers",31.0301,"Parks, Recreation, and Leisure Facilities Management, General." ],
  ["11-1021","General and Operations Managers",31.0399,"Parks, Recreation, and Leisure Facilities Management, Other." ],
  ["11-1021","General and Operations Managers",44.0401,"Public Administration." ],
  ["11-1021","General and Operations Managers",52.0101,"Business/Commerce, General." ],
  ["11-1021","General and Operations Managers",52.0201,"Business Administration and Management, General." ],
  ["11-1021","General and Operations Managers",52.0206,"Non-Profit/Public/Organizational Management." ],
  ["11-1021","General and Operations Managers",52.0212,"Retail Management." ],
  ["11-1021","General and Operations Managers",52.0215,"Risk Management." ],
  ["11-1021","General and Operations Managers",52.0701,"Entrepreneurship/Entrepreneurial Studies." ],
  ["11-1021","General and Operations Managers",52.0704,"Social Entrepreneurship." ],
  ["11-1021","General and Operations Managers",52.0801,"Finance, General." ],
  ["11-1021","General and Operations Managers",52.081,"Financial Risk Management." ],
  ["11-1021","General and Operations Managers",52.1101,"International Business/Trade/Commerce." ],
  ["11-1021","General and Operations Managers",52.1301,"Management Science." ],
  ["11-1031","Legislators",44.0401,"Public Administration." ],
  ["11-1031","Legislators",44.0501,"Public Policy Analysis, General." ],

Javascript

<link href="/careerconnections/css/DataTables/datatables.css" type="text/css" rel="stylesheet">
<link href="/careerconnections/css/DataTables/datatables.additions.css" type="text/css" rel="stylesheet">
        <link href="/careerconnections/css/DataTables/Responsive-2.0.2/css/responsive.dataTables.css" type="text/css" rel="stylesheet">
        <link href="/careerconnections/css/DataTables/DataTables-1.10.11/css/jquery.dataTables.css" type="text/css" rel="stylesheet">
                
        <script type="text/javascript" src="/careerconnections/js/DataTables/DataTables-1.10.11/js/jquery.dataTables.js"></script>
        <script type="text/javascript" src="/careerconnections/js/DataTables/Responsive-2.0.2/js/dataTables.responsive.js"></script>




    <script>
        $(document).ready(function() {

            jQuery.extend( jQuery.fn.dataTableExt.oSort, {
                "my-currency-pre": function(a) {
                    return parseFloat(a.replace(/[ $,]/gi, ''));
                },
                "my-currency-asc": function(a,b) {
                    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
                },
                "my-currency-desc": function(a,b) {
                    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
                }
            });


                $('#example').DataTable( {
                    "ajax": '/careerconnections/data/DataTables/soc_cip_crosswalk_2020.txt',
                    "order": [[ 1, "desc" ]],
                    "lengthMenu": [ 25, 50, 75, 100 ],
                    /* Only needed when my-currency definition should be used on columns targeted by 0-indexed list of "aTargets"
                                        "aoColumnDefs": [
                      {"sType": "my-currency", "aTargets": [3,4,5]}
                    ]
                                        */
                } );
        } );
    </script>

Column definitions

<p>&nbsp;</p>
<table id="example" class="display responsive" width="100%" cellspacing="0">
<thead>
<tr><th>2018 SOC Code</th><th>2018 SOC Title</th><th>2020 CIP Code</th><th>2020 CIP Title</th></tr>
</thead>
<tfoot>
<tr><th>2018 SOC Code</th><th>2018 SOC Title</th><th>2020 CIP Code</th><th>2020 CIP Title</th></tr>
</tfoot>
</table>
<p>&nbsp;</p>
<p>&nbsp;</p>

kthorngren

Are you looking for something like what RowGroup does?

Kevin

mac173173

I am not sure. I looked at the examples and they don't look like what I am aiming at. Like I said, I am not a coder.
I tried putting all the CIP data in the same row as the SOC data, but the table will not load.
Do I need to put the code numbers within the same field as the titles? Like "11-1011, Chief Executives", instead of "11-1011","Chief Executives",?

tangerine

Can you give us a clearer picture of what you are aiming at?

kthorngren

Here is an example of your data with RowGroup:
http://live.datatables.net/famaguho/1/edit

The rowgroup header can be customized to show the SOC code (with that column hidden), etc.

I tried putting all the CIP data in the same row as the SOC data,

Not sure I understand the goal. Are you saying you want one row for Chief Executives, for example, then on that row show the 9 rows of data in one row? Datataables needs to have a consistent number of columns for each row. Maybe you can show us an example of what you want displayed. Maybe a screenshot of one or two rows of data from a spreadsheet.

Kevin

mac173173

I will try to explain. The SOC Titles are job titles. The CIP Titles are training and education related to the job title.
I want the SOC job titles on a single row with all the associated CIP titles beside them.
The problem is that each SOC job title has a different number of CIP titles, so the number of columns will vary with each SOC job title.
SOC, CIP, CIP, CIP, CIP, CIP
SOC, CIP, CIP
SOC, CIP, CIP, CIP, CIP, CIP, CIP, CIP
SOC, CIP
It varies between one CIP to over 20 in some cases.

mac173173

" Datatables needs to have a consistent number of columns for each row."

That is my downfall. I may have to restructure my data.
If I have columns with no data, will they be displayed as empty fields?

kthorngren

SOC, CIP, CIP, CIP, CIP, CIP

For Datatables to work each row would have two columns, something like this:

|SOC Title   | CIP Title                         |
-------------------------------------------------|
|SOC         | CIP, CIP, CIP, CIP, CIP           |
|SOC         | CIP, CIP                          |
|SOC         | CIP, CIP, CIP, CIP, CIP, CIP, CIP |
|SOC         | CIP                               |

Would this work?

Kevin

mac173173

kthorngren, that looks like what I want. I would have to include the codes and titles together, but that can work.
Can you show me how you did that?
I am going to try both the row group and this method and run them by my data analyst to see which he likes better.

kthorngren

The data object you have will need to be changed. Don't know if you can change it at the data source but it needs to look like this:

[
  ["Chief Executives", ["Public Administration.","Business/Commerce", ....],
  ["General and Operations Managers", ["Veterinary Office Management/Administration.","Parks", "Recreation",...],
...
]

In this example I converted the data in Javascript:
http://live.datatables.net/senifuyi/1/edit

Since you are using ajax you can use ajax.dataSrc as a function and the code I used in that function to convert the json data. See the last example in the docs. Sorry I can't provide a specific example for this as I don't have an ajax data source I can use to demo.

Kevin

mac173173

Thanks, I am going to try both methods and see which works.

I will post back when I am done.

mac173173

Ok, not having any success here. This is how I tried to integrate your code into what I have.

<link href="/careerconnections/css/DataTables/datatables.css" type="text/css" rel="stylesheet">
<link href="/careerconnections/css/DataTables/datatables.additions.css" type="text/css" rel="stylesheet">
        <link href="/careerconnections/css/DataTables/Responsive-2.0.2/css/responsive.dataTables.css" type="text/css" rel="stylesheet">
        <link href="/careerconnections/css/DataTables/DataTables-1.10.11/css/jquery.dataTables.css" type="text/css" rel="stylesheet">
                
        <script type="text/javascript" src="/careerconnections/js/DataTables/DataTables-1.10.11/js/jquery.dataTables.js"></script>
        <script type="text/javascript" src="/careerconnections/js/DataTables/Responsive-2.0.2/js/dataTables.responsive.js"></script>




    <script>
        $(document).ready(function() {

            jQuery.extend( jQuery.fn.dataTableExt.oSort, {
                "my-currency-pre": function(a) {
                    return parseFloat(a.replace(/[ $,]/gi, ''));
                },
                "my-currency-asc": function(a,b) {
                    return ((a < b) ? -1 : ((a > b) ? 1 : 0));
                },
                "my-currency-desc": function(a,b) {
                    return ((a < b) ? 1 : ((a > b) ? -1 : 0));
                }
            });  


                $('#example').DataTable( {
                    "ajax": '/careerconnections/data/DataTables/soc_cip_crosswalk_2020.txt',
                    "order": [[ 1, "asc" ]],
                    "lengthMenu": [ 25, 50, 75, 100 ],
                    
                     columnDefs: [
                        {
                          targets: 1,
                          visible: false
                        }
                      ],
                      rowGroup: {
                       dataSrc: 1
                      }
                } );
    </script>

I am getting the headers, but no data. No error code, but no data.
I don't think I mentioned that I am working inside a framework called OpenText. I cannot put the data inside the same page, it must be called using ajax, like in the original code I posted.
Can you tell where I got this wrong?

mac173173

In case it helps, here is a link to a page successfully using DataTables. It uses the code I showed in the original question.

https://careerconnections.nj.gov/careerconnections/prepare/skills/demand/demand_occupations_list.shtml

kthorngren

Like I said you need to use the ajax.dataSrc option to manipulate the data into something Datatables can use. You should be able to reuse, maybe with some modifications, the code I provided in the ajax.dataSrc function.

Kevin