rlefever

Hi Peter,

Yes, your prompting led me to realise a dumb mistake. The table had the data types wrong and so I will need to rebuild the table with the correct type. In the meantime, if anyone else makes a similar mistake, you can ‘cludge’ it by changing the data type while queerying it with ‘CAST’.

SELECT CAST(jlr_fsqm_direct_6.updated AS DATE) AS 'Date',
       CAST(jlr_fsqm_direct_6.freetype2 AS SIGNED) AS 'ORS',
       CAST(jlr_fsqm_direct_6.freetype3 AS SIGNED) AS 'SRS',
       CAST(jlr_fsqm_direct_6.mcq1 AS SIGNED) AS 'Ind',
       CAST(jlr_fsqm_direct_6.mcq2 AS SIGNED) AS 'Int',
       CAST(jlr_fsqm_direct_6.mcq3 AS SIGNED) AS 'Soc',
       CAST(jlr_fsqm_direct_6.mcq4 AS SIGNED) AS 'GenO',
       CAST(jlr_fsqm_direct_6.mcq5 AS SIGNED) AS 'Rel',
       CAST(jlr_fsqm_direct_6.mcq6 AS SIGNED) AS 'G&T',
       CAST(jlr_fsqm_direct_6.mcq7 AS SIGNED) AS 'A&M',
       CAST(jlr_fsqm_direct_6.mcq8 AS SIGNED) AS 'GenS',
       CAST(jlr_fsqm_direct_6.pinfo4 AS SIGNED) AS 'ID'
FROM jlr_fsqm_direct_6

thank you again for everyone’s help

Thank you very much for taking a look at this.

Yes, I did look at that format hint pop up but what puzzles me is that my first column is a date and the rest are numbers so I feel like my data is conforming to the type that it requires and yet I still get that error. Is there any other way I can check that I am making the data type explicit? Am I not specifying its type in the selection correctly or is there some very specific format it wants to see the date in?

Thank you again for all your help Peter, it’s really appreciated, especially on a Sunday!!! ??

Hi again Kim,

Sorry to bother you but did you have any ideas?

Many thanks

Robin

Ah thank you so much,

So yes, there is a front end table where we can select a particular client ID and see the raw data in a table. I then want to be able to chart this data as a line chart.

When I export this data as a scv and bring it into grid.is I can make a wonderful line chart like this but this is too many steps for people on my site to use. I want them to just press a button, preferably next to the export buttons above, and just be able to get a similar line chart from WP Data Access.

When I select a table view in the chart function of WP Data Access I get the data I want displayed in a table

And it displays fine as a table:

But then when I say I want the same data displayed as a line chart

I get this error code:

Thank you so much for taking a look at all this. It’s really very much appreciated!

Best

Robin

Thank you so much for your help Aaron!

I’m sorry I am probably asking you obvious questions ??

I have a ‘premium table’ which selects a single client ID:

SELECT jlr_fsqm_direct_6.updated as ‘Date’
, jlr_fsqm_direct_6.freetype2 as ‘ORS’
, jlr_fsqm_direct_6.freetype3 as ‘SRS’
, jlr_fsqm_direct_6.mcq1 as ‘Ind’
, jlr_fsqm_direct_6.mcq2 as ‘Int’
, jlr_fsqm_direct_6.mcq3 as ‘Soc’
, jlr_fsqm_direct_6.mcq4 as ‘GenO’
, jlr_fsqm_direct_6.mcq5 as ‘Rel’
, jlr_fsqm_direct_6.mcq6 as ‘G&T’
, jlr_fsqm_direct_6.mcq7 as ‘A&M’
, jlr_fsqm_direct_6.mcq8 as ‘GenS’
, jlr_fsqm_direct_6.pinfo4 as ‘ID’
FROM jlr_fsqm_direct_6

I would like to have a button to send this specific data to make a line chart.

I have also been struggling with charting. Whenever I try and chart the query:

SELECT DATE(jlr_fsqm_direct_6.updated) AS ‘Date’,
jlr_fsqm_direct_6.freetype2 AS ‘ORS’,
jlr_fsqm_direct_6.freetype3 AS ‘SRS’,
jlr_fsqm_direct_6.mcq1 AS ‘Ind’,
jlr_fsqm_direct_6.mcq2 AS ‘Int’,
jlr_fsqm_direct_6.mcq3 AS ‘Soc’,
jlr_fsqm_direct_6.mcq4 AS ‘GenO’,
jlr_fsqm_direct_6.mcq5 AS ‘Rel’,
jlr_fsqm_direct_6.mcq6 AS ‘G&T’,
jlr_fsqm_direct_6.mcq7 AS ‘A&M’,
jlr_fsqm_direct_6.mcq8 AS ‘GenS’
FROM jlr_fsqm_direct_6
WHERE jlr_fsqm_direct_6.pinfo4 = 7236

I keep getting the following error:

Data column(s) for axis #0 cannot be of type string×

I’ve been trying to adjust the format and type of the first column to see if I can assure the charting part that the data is of type ‘date’ but whatever I try doesn’t work. I have searched a lot for this on the internet ( Stack overflow etc ) but I can’t find a solution:

SELECT DATE_FORMAT(jlr_fsqm_direct_6.updated, ‘%Y-%m-%d’) AS ‘Date’,
jlr_fsqm_direct_6.freetype2 AS ‘ORS’,
jlr_fsqm_direct_6.freetype3 AS ‘SRS’,
jlr_fsqm_direct_6.mcq1 AS ‘Ind’,
jlr_fsqm_direct_6.mcq2 AS ‘Int’,
jlr_fsqm_direct_6.mcq3 AS ‘Soc’,
jlr_fsqm_direct_6.mcq4 AS ‘GenO’,
jlr_fsqm_direct_6.mcq5 AS ‘Rel’,
jlr_fsqm_direct_6.mcq6 AS ‘G&T’,
jlr_fsqm_direct_6.mcq7 AS ‘A&M’,
jlr_fsqm_direct_6.mcq8 AS ‘GenS’
FROM jlr_fsqm_direct_6
WHERE jlr_fsqm_direct_6.pinfo4 = 7236

So sorry to ask but do you have any idea what I’m doing wrong here also?

Many thanks again

Robin