Array add column with 'days until due'

[hrcap]

Afternoon All

I hope that everyone is well.

I currently have some data which I will simplify below:

event date
Football 2019-10-10
Disco 2020-01-02


this data is held within a SQLIte database, each time I pull this data from the database I wish to add a column to it which will display the number of days until the event, can anybody shed some light on how I may go about doing this?



Many Thanks

[dunbarx]

Hi.

You want to use the "convert" command with the "dateItems" keyword. Read about these in the dictionary.

Your "event date", I assume is of the same format as, say, the football date? If so, (pseudo)

Code: Select all

on mouseUp
  put "2019-10-16" into tEventDate
  put "2019-10-19" into tFootballDate

  replace "-" with "," in tEventDate ; replace "-" with "," in tFootballDate
  put ",0,0,0,0" after tEventDate ; put ",0,0,0,0" after tFootballDate
  
  convert tEventDate to seconds ; convert tFootballDate to seconds
  put tFootballDate - tEventDate into tSecondsToGo
  answer "There are" && round(tSecondsToGo / 86400) && "days until the event"
end mouseUp

Untested, and may need refinement, since this does not consider the time of day when you run this. But the point is to teach the method, and introduce new LC words.

Write back with complaints.

Craig

[hrcap]

Hi Craig

Thank you very much for taking the time to reply.

Unfortunately I may not have written the question I was asking as clearly as it could have been.



I have two columns of data:

the first column = "Event"
under the column title "Event" are the actual events i.e.

Football
Disco




The second column = "Date"
under the column title "Date"are the dates that the events are taking place i.e.

2019-10-10 (date that the football is taking place)
2020-01-02 (date that the disco is taking place)


I need to somehow:

- visit each row
- when in that row subtract todays date from the date of the event
- Add a column which will contain for each row the number of days until the event


(the bit that I am most stuck on is how to visit each row and put the days until the event into a new column, rather than the date calculation itself)


Apologies if the above doesnt read very clearly.


Many Thanks

Hadleigh

[FourthWorld]

Arrays do not have rows and columns. If this is working well as delimited text, why does it need to be transformed into an array?

[dunbarx]

What Richard said..

Arrays, that is, LiveCode array variables, have vast power and speed. But they operate a bit "invisibly", in that it is not easy to track their changes and progress. Whereas a well-ordered structure of columns and rows, just as in a spreadsheet, can be managed and viewed realTime using tabs as field delimiters and CR as row delimiters. This is what I call "in the clear".

Do you have this sort of experience under your belt? That for example, you can isolate and manage even a single "cell" by calling out the line that cell is in, and with the itemDel set to "tab", the column it is in. If you do, then it is a short hop to being able to navigate around an "array" (not an array variable) of data.

I do not consider your last post a real complaint. Try again.

Craig

[SparkOut]

I can't give demo or code with phone keyboard here, but I would suggest making the SQL query do the work and return the constructed value as a third column along with the other data. Maybe tomorrow if you haven't figured it out yourself, or someone else hasn't chimed in.

But Craig is giving you best advice as to learning LiveCode.

[AxWald]

Hi,

[...] this data is held within a SQLIte database, each time I pull this data from the database I wish to add a column to it which will display the number of days until the event [...]

1. Naming a field in a database 'Date' isn't a very good idea. For now I'll use 'f_event' and 'f_date' ...
   .
2. Don't even start to bother LC with such. Read your database manual and use:

   Code: Select all

   SELECT f_event AS 'Event', f_date AS 'Date', ROUND(STRFTIME('%J',f_date) - STRFTIME('%J','now'),0) AS 'DaysUntil' FROM t_table WHERE ...

Have fun!

[SparkOut]

I would suggest making the SQL query do the work <snip>...

or someone else hasn't chimed in.

There you go. Thanks AxWald!

[hrcap]

Thats Brilliant

Thank you very much everyone. I had been thinking of attacking the problem by

- querying the database to get all of the required records
- then looping through each row of data to carry out the number of days calculation
- then adding the number of days to the end of that row of data

I did not realise that it is possible to not only pull existing data via an SQL query but also carry out calculations at the same time. I have used the following for the solution:

Code: Select all

   --selects all of the data and then adds the number of days until expiry on to the end of each row of data
   put "SELECT *, ROUND( STRFTIME('%J', expiry_date) - STRFTIME('%J', 'now'), 0 ) AS 'DaysUntil' FROM QUALIFICATIONMODULES WHERE _kf_uuid_qualification = '7cbe82e5-2f6c-4623-bddd-e3855969aa43'" into t_sql
   
   
   --get revQueryDatabase (k_id_connection_local, t_sql)
   get revDataFromQuery (tab, return, k_id_connection_local, t_sql)
   put it into t_data
   answer t_data
   

*** A question I do have however is that the number of days is being returned with one decimal place e.g. 188.0 days, how can I get rid of the .0?

Many Thanks

[bogs]

The result will always break down to characters, so you could do a simple test to see if there is a decimal point.

For instance, something like (untested) -

Code: Select all

//////
	if character -2 of t_data is "." then delete character -2 to -1
	answer t_data
//////

[hrcap]

Thanks Bogs

However if the method of searching for a decimal point was used it would again mean that the data should have to be looped through line by line to find the decimal point near the end of each line.

Is there any reason why the ROUND part of the SQL query wouldn’t be working?

Many Thanks

[SparkOut]

Try

Code: Select all

ROUND( STRFTIME('%J', expiry_date) - STRFTIME('%J', 'now') )

instead of

Code: Select all

ROUND( STRFTIME('%J', expiry_date) - STRFTIME('%J', 'now'), 0 )

in that part of the query

[AxWald]

Hi,

strange. The SQLite Documentation says:

The round(X,Y) function returns a floating-point value X rounded to Y digits to the right of the decimal point. If the Y argument is omitted, it is assumed to be 0.

And using SQLite Manager I get proper whole numbers ...

SQL can be strange, and SQLite can be even stranger ;-)

Btw., rounding is needed because of the 'J' - it's Julian days, and from here come the fractional numbers:

Code: Select all

SELECT STRFTIME('%J','now')
=> 2458685.164379838

Have fun!

[hrcap]

Thank you very much all

With some further research I have found that the word 'cast' is necessary within the sql statement to remove the decimal place... as below:

Code: Select all

   --selects all of the data and then adds the number of days until expiry on to the end of each row of data
   put "SELECT *, cast(ROUND( STRFTIME('%J', expiry_date) - STRFTIME('%J', 'now') )as int) AS 'DaysUntil' FROM QUALIFICATIONMODULES WHERE _kf_uuid_qualification = '7cbe82e5-2f6c-4623-bddd-e3855969aa43'" into t_sql

Many Thanks