×
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

missing acd copied to a new table

missing acd copied to a new table

missing acd copied to a new table

(OP)
attached is table master want to find out which acd is missing from 1 to end orderwise which is copied to a table like in above acd 1343,1346,1349,1352... are missing
also if you can help me to tell me in field descpt for bsgroup=100 details in descpt field starts with number like 10- or 100- or 501- can you please help and tell me how to write the missing numbers in descpt alsoin a new table
thanks
regards

RE: missing acd copied to a new table

What do you mean with "missing orderwise"?

I mean, if you say 3 is missing from 1,2,4 then it still is missing from 4,1,2 or 2,1,4 or 1,4,2, the order doesn't have anything to do with a missing value. Your acd values have large gaps, I don't think they are a simple numeric sequence, but a hierarchical ID, you have to explain things in more detail, before you can expect a solution.

So - still - all in all I think it would help to understand your problem, if you just phrase this a bit better.

Not to speak of the other problem of bsgroup, that seems completely unrelated.

Chriss

RE: missing acd copied to a new table

I'm also having difficulty understanding your question. In particular, what is "acd"? Activity-centered design? Automatic call distributor? Adelaide College of Divinity?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: missing acd copied to a new table

First part of your question; are you asking where the records with the missing values for the field acd are? or do you want some code to reveal the gaps?

The second part of your question is beyond me, I can see a field bsgroup and can filter on it, but I can't see what number you want in the descrpt field?

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0

RE: missing acd copied to a new table

I can only hope these are fake data and not the real data.

Mr. Bond, you may have created a new problem.

Regards, Gerrit

RE: missing acd copied to a new table

B.James,

After looking at your table, it seems to me you could make a list of ALL values you believe should be in the Acd field. Then index Master.dbf on the Acd field, loop through your list of values & confirm each one exists in Master.dbf or if not, add it to the no-find list.

Use SEEK, or better yet, SET RELATION TO.

Steve

RE: missing acd copied to a new table

Extremely scrappy code to locate missing ACD values

CODE

CLEAR
SET ALTERNATE TO ("REPFILE.TXT")
SET ALTERNATE ON

USE MASTER EXCLUSIVE
INDEX ON STR(VAL(ACD),5,0) TAG MASTER
COPY TO MISSING STRU
SELECT 0
USE MISSING EXCLUSIVE
SELECT MASTER
GO BOTTOM
m.MAXACD = VAL(ACD)
GO TOP
DO WHILE .NOT. EOF() .AND. VAL(ACD)=0
	SKIP
ENDDO
IF !EOF()
	FOR m.INDEXKEY = VAL(ACD) TO m.MAXACD
		SELECT MASTER
		SEEK (STR(m.INDEXKEY,5,0))
		IF !FOUND()
			? STR(m.INDEXKEY,5,0)
			SELECT MISSING
			APPEND BLANK
			REPLACE ACD WITH ALLTRIM(STR(m.INDEXKEY,5,0))
		ENDIF
	NEXT
ENDIF
SET ALTERNATE OFF
SET ALTERNATE TO
SELECT MISSING
GO TOP
BROWSE 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0

RE: missing acd copied to a new table

(OP)
thank you griffMG was a great help it works perfect appreciate your help
thank you regards

RE: missing acd copied to a new table

(OP)
thank you Chris Miller, Mike Lewis, Gerritt Broekhuis, Steve Meyerson and tamarGranor for your help i am sorry i could not explain to my best capablilities Griff has solved it and i appreciate your help i will take utmost care of submitting my post in future thank you for your advices
regards

RE: missing acd copied to a new table

@Tamar

Interesting document, I think my use of a for...next loop is analogous to creating a cursor of dates in your example
for finding missing values. I have to say my SQL skills are way to weak to solve this problem, I'm too much of
a procedural mindset!



Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0

RE: missing acd copied to a new table

Well, if you just want any value between 1 and max(acd) that's missing, that's done easier, no reason to flex, though.

I just wonder why you do a loop for finding the first non 0 val(acd) instea of just locating it?

CODE

*GO TOP
*DO WHILE .NOT. EOF() .AND. VAL(ACD)=0
*	SKIP
*ENDDO
LOCATE FOR NOT VAL(ACD)=0 

It will save more time not to print the mising values, so I should not even address this part as it's less imnportant to save a bit time and all is already done, but I really wonder why you do everything with loops that can also be doe in a single step.

Chriss

RE: missing acd copied to a new table

Habit Chris, it takes very little longer and is clear, to me, when I look back at it later.
He only had 20 or so text entries, so I doubt it would be a significant difference in speed.

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0

RE: missing acd copied to a new table

Indeed it's not about speed, but it's not at all explainable by procedural mindset. And if you argue by looking into the data, you could also just have programmed the following for loop start vaule 1 instead of determining the minimum numeric value.

Chriss

RE: missing acd copied to a new table

True

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0

RE: missing acd copied to a new table

Never mind, Griff, just don't put up self harm or apologies in advance, it's completely unnecessary and actually will just raise what you want to avoid, comments on how you could improve. And I then wonder what's then putting you in defending how you must have done things, when you just could take it as improvement of your skills, when you get pointed out a shortcut of your coding style instead of a completely different solution.

Chriss

RE: missing acd copied to a new table

I am sorry, Griff, that you are being subjected to this unmerited criticism.
Your responses to queries from members are always helpful - Andrew

RE: missing acd copied to a new table

Andrew,

I see your feelings are hurt, never intended that, sorry.

Chriss

RE: missing acd copied to a new table

(OP)
sorry sirs if for me there was misunderstanding

RE: missing acd copied to a new table

Hi,

My suggestion

CODE -->

LOCAL ARRAY laAllValues[1]

CREATE CURSOR csrValues (iValue I)

FOR i = 1 TO 25
	INSERT INTO csrValues VALUES (INT(RAND() * 50))
ENDFOR 

SELECT MAX(iValue), MIN(iValue) FROM csrValues INTO ARRAY laAllValues

CREATE cursor csrAllValues (iValue I)

FOR i = 0 TO laAllValues[1] - laAllValues[2]
	INSERT INTO csrAllValues VALUES (laAllValues[2] + i)

ENDFOR

SELECT * FROM csrAllValues WHERE iValue NOT in (Select * FROM csrValues) ORDER BY 1 INTO CURSOR csrMissingValues

BROWSE 

CLOSE ALL

RETURN 

or with the ACD data from the MASTER dbf

CODE -->

LOCAL ARRAY laAllValues[1]

*!*	CREATE CURSOR csrValues (iValue I)

*!*	FOR i = 1 TO 25
*!*		INSERT INTO csrValues VALUES (INT(RAND() * 50))
*!*	ENDFOR 

*!*	SELECT MAX(iValue), MIN(iValue) FROM csrValues INTO ARRAY laAllValues

CLOSE ALL 

USE master

SELECT MAX(VAL(acd)), MIN(VAL(acd)) FROM master INTO ARRAY laAllValues

*!*	Skips the ACD values that are not numeric

CREATE cursor csrAllValues (iValue I)

FOR i = 0 TO laAllValues[1] - laAllValues[2]
	INSERT INTO csrAllValues VALUES (laAllValues[2] + i)

ENDFOR

SELECT * FROM csrAllValues WHERE iValue NOT in (Select VAL(acd) FROM master) ORDER BY 1 INTO CURSOR csrMissingValues

BROWSE 

CLOSE ALL

RETURN 

hth

Mark

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