×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

COUNT to Dimension ARRAY ?
2

COUNT to Dimension ARRAY ?

COUNT to Dimension ARRAY ?

(OP)
I have the following code in my application which works as required.

Is there a way of COUNTing into my Dimension ARRAY wagi(10) instead of first storing it in wagi_count and then storing it into my Dimension ARRAY wagi(10) ?

CODE -->

PUBLIC ARRAY wagi(10)

SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") INTO ARRAY wagi_count 	
STORE wagi_count TO wagi(1)

SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") INTO ARRAY wagi_count 	
STORE wagi_count TO wagi(2)

SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") INTO ARRAY wagi_count 	
STORE wagi_count TO wagi(3)

SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") INTO ARRAY wagi_count 	
STORE wagi_count TO wagi(4) 

Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

David,

You could try this:

CODE -->

SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") ; 	
  UNION  SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") ;
  UNION  SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") ;	
  UNION  SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") INTO ARRAY wagi_count 


but I'm not sure that it is any better than your original code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: COUNT to Dimension ARRAY ?

A couple of other improvements to your original code:

- Don't use CTOD() when expressing a date literal. Better to do DATE(2017, 1, 1). That way, you won't get tripped up by SET DATE settings.

- Although STORE wagi_count TO wagi(1) will work as expected, it is a bit more concise to do wag1(1) = wgi_count.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: COUNT to Dimension ARRAY ?

(OP)
Mike,

Thank you for you reply.

I tried using your code but didn't get the expected result.

If, say, the Count for DATE_160 was 21, DATE_80 was 37, DATE_60 was 0, DATE_40 was 53 I would have expected :-

wagi_count (1) to contain 21 actual = 0
wagi_count (2) to contain 37 actual = 21
wagi_count (3) to contain 0 actual = 37
wagi_count (4) to contain 53 actual = 53

So it looks like it's storing them in Value order not actual order?

I've applied your recommendations to my code. The CTOD() must stem from my early days with VFP. Since I started to re-construct my project I've been applying experience I've gained over the years to simplify and make the code more efficient. One of the major gains is reducing the number of PUBLIC Variables from literally 100's down to zero at the moment (excepting the use of 3 PUBLIC MY ARRAY's). I achieved this by passing PARAMETERS and using PROPERTY on FORMS.



Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

Steve,

I can see what's happening here.

As you say, you have got the correct results but in the wrong order. In general, you can't determine the order in which SQL SELECT returns its results, unless you include an ORDER BY clause. With no ORDER BY the results can - to all intents and purposes - be considered to be in a random order.

In this caes, a possible solution would be to add an extra column to the array to force the order. Something like this:

CODE -->

SELECT 1, COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") ; 	
  UNION SELECT 2, COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") ;
  UNION SELECT 3, COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") ;	
  UNION SELECT 4, COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") ;
    ORDER BY 1 INTO ARRAY wagi_count 

That would then give you a 2-column array. The first column contains the sequence number (1, 2, ...); the second contains the required data, in the required order.

Given that you want a one-colum array, you could do the above SELECT into a temporary array, then copy the second column into wagi_count.

However, although my solution is still more concise than your original code, I can't help thinking that your code is more readable. I would be inclined to stay with what you already have (after making the CTOD() and STORE changes I mentioned earlier).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: COUNT to Dimension ARRAY ?

(OP)

Quote (Mike Lewis)

However, although my solution is still more concise than your original code, I can't help thinking that your code is more readable. I would be inclined to stay with what you already have (after making the CTOD() and STORE changes I mentioned earlier).

Thank you for your reply.

I'd more or less decided to stick with what I've got after I applied your recommendations to my code, but out of courtesy I tried your code and reported my findings.

CODE -->

SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > DATE(2017, 1, 1) INTO ARRAY wagi_count 	
wagi(4) = wagi_count	&&	 40 Metres 

Thank you for your valued advice.

Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

That's fine, David. You made the right choice. That said, I hope that you (and perhaps others) found my suggestion of some moderate educational value.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: COUNT to Dimension ARRAY ?

In general, SQL is better with rows, not columns. So when you'd store your dates in a single date column "DateN" and store the number n (40,60,80,160) as extra field "dategroup", you can group by. Mikes last solution then wouldn't need to create a numeric field on the fly to order by it, it wouldn't even need to union 4 queries, it would end up as one simple query.

CODE

Select Count(*) From aTable where DateN > DATE(2017, 1, 1) Group by Dategroup Order By Dategroup 
Mike's query could be condensed to this, if his 1,2,3,4 was already in the data and a single row with date40,60,80, 160 would be split into 4 groups of rows with (dateN,40), (dateN,60), (dateN,80) and (dateN,160).

I know your data isn't stored that way and you likely have stored your data your way for decades and programmed for its structure, so a data structure change needs a lot of code changes.
But you see that's how SQL is designed to work best with data in rows, not in columns.

Or see it this way: As group by works this way, it's nice to have columns in the data which can be used for group by. So don't create arrays of fields, just because that's better to read in the end (just like wide scree movies - but also note text is still easier to read in quite narrow columns, even in large newspapers).

I guess you'll say then a lot of other fields would repeat 4 time. No, not if you store them separate and let your 4 new records point back to them AKA 1:n relation.

Chriss

RE: COUNT to Dimension ARRAY ?

(OP)

Quote (Mike Lewis)

I hope that you (and perhaps others) found my suggestion of some moderate educational value.

Very much so.

Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

(OP)

Quote (Chris Miller)

In general, SQL is better with rows, not columns.

Thank you for your input Chris.

I am re-building one of my applications at the moment and don't really want to make data structure changes at this stage, but I will take on board your advice for the next time I'm experimenting with MySQL.

Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

UNION sorts the data.

Tamar

RE: COUNT to Dimension ARRAY ?

I think this experiment answers it:

CODE

USE SYS(2005) AGAIN ALIAS dummy
SELECT 3 FROM dummy WHERE RECNO()<4 ;
union ;
SELECT 2 FROM dummy WHERE RECNO()<3 ;
union ;
SELECT 1 FROM dummy WHERE RECNO()<2 

The result is 1,2,3, not 3,2,1.

The question is, what's when you want to order by second column or how well the code shows that it sorts. I think it's not bad to have the order by even when it's optional, just for sake of seeing what happens. Same story, I guess, group by also sorts.

And on the other side: UNION also eliminates doubles and UNION ALL does not sort.


Chriss

RE: COUNT to Dimension ARRAY ?

(OP)
The following code produced 0,11,12 and then ERROR Subscript outside defined range.

I was expecting 0,11,0,12 as confirmed with my original code.

CODE -->

SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") ; 	
UNION;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") ;
UNION;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") ;
UNION;  	
SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") INTO ARRAY wagi_test 

FOR a = 1 TO 4
	MESSAGEBOX(wagi_test(a))
NEXT 

The following code produced the same results & order as the above code.

CODE -->

SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > ctod("01-01-2017") ; 	
UNION;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > ctod("01-01-2017") ;
UNION;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > ctod("01-01-2017") ;
UNION;  	
SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > ctod("01-01-2017") INTO ARRAY wagi_test 

FOR a = 1 TO 4
	MESSAGEBOX(wagi_test(a))
next 

Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

Yes, that's because UNION removes doubles AND sorts.

Use UNION ALL instead.

Or, on the othr side, do as Mike suggested to add an index field, in the order you want and to make records unique, so no double is removed. That also ties to the usual group by you could use with data in a "normal" structure.

Chriss

RE: COUNT to Dimension ARRAY ?

David, it would help us to understand what is going on here if we knew a bit more about the data. In particular, whether the four date fields in csrWAGI_wkd are mutually exclusive. IAnd in a given record in csrWAGI_wkd, can more than one of the four meet the criterion for the date test?

Can you post a mimimum amount of representative data that would allow us to run the code and try to reproduce the promblem?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: COUNT to Dimension ARRAY ?

I'd also be interested in the data before it goes into the csrWAGI_wkd cursor. Because I think you create those Date_N fields and so the counting would be easier on the initial data than it is on the cursor.

Chriss

RE: COUNT to Dimension ARRAY ?

(OP)

Quote (Chris Miller)

Yes, that's because UNION removes doubles AND sorts.

Use UNION ALL instead.

Thank you Chris, changing from UNION to UNION ALL produced the desired result.

Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

(OP)

Quote (Mike Lewis)

David, it would help us to understand what is going on here if we knew a bit more about the data.

The Data is used to record Amateur Radio Contacts with stations operating from a particular National Grid Reference Number based on a 10kM x 10kM Square. See http://wab.intermip.net/Introduction.php for further information.

The Contact could be made using a number of different 'Band of Frequencies" hence the Date_160 denotes that a contact was made on the 160 Metre Band and Date_40 the contact wade made on the 40 Metre Band.

So the Database will contain a list of all the 10kM x 10kM Squares of Great Britain and each square will have a list of 'Band of Frequencies". If I make contact with a Station in Keswick the NGR would be NY22 and say the Band of operation is 80 Metres then an entry will be made under NY22 with a Date_80 entry.

Only one contact needs to be recorded for each NGR / Band combination, although in reality all contacts are recorded in a separate database the Logbook.


Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

Consider converting the count(*) into a string padded with leading zeros. Should sort properly then.

Mike Yearwood - Former FoxPro MVP

RE: COUNT to Dimension ARRAY ?

(OP)

Quote (myearwood)

Consider converting the count(*) into a string padded with leading zeros. Should sort properly then.

This is the modified code with no sorting, which is what I was looking for. So wagi(3) will always show the COUNT for Date_60

CODE -->

SELECT COUNT(*) FROM csrWAGI_wkd where Date_160 > DATE(2017, 1, 1) ;
UNION ALL;  	
SELECT COUNT(*) FROM csrWAGI_wkd where Date_80 > DATE(2017, 1, 1) ;
UNION ALL;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_60 > DATE(2017, 1, 1) ;
UNION ALL;
SELECT COUNT(*) FROM csrWAGI_wkd where Date_40 > DATE(2017, 1, 1) ;
INTO ARRAY wagi 

Regards,

David.

Recreational Developer / End User of VFP.

RE: COUNT to Dimension ARRAY ?

That makes sense, David. So what you have got is the code that I originally suggested, but with UNION ALL instead of plain UNION. I can see now that that overcomes the problem of getting the counts in order, but without having to resort to an extra column in the array.

Mike






__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: COUNT to Dimension ARRAY ?

(OP)
It is indeed based on your code Mike, with as you say, the UNION Statement as suggested by Chris. Far neater than my original code.

In each ARRAY, I use several in my application, I have 10 COUNTS in each. I just used 4 COUNTS as an example.

Regards,

David.

Recreational Developer / End User of VFP.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close