missing acd copied to a new table 1

[Bondjames]

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

 

[Chris Miller]

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

 

[Mike Lewis]

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

 

[GriffMG]

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 [Smile]ing

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
​

 

[Gerrit Broekhuis]

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

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

Regards, Gerrit

 

[Steve Meyerson]

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

 

[TamarGranor]

Check the section "Finding missing values" in this paper:

http://tomorrowssolutionsllc.com/ConferenceSessions/Using SQL to Solve Common Problems (2019).pdf

Tamar

 

[GriffMG]

Extremely scrappy code to locate missing ACD values

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 [Smile]ing

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
​

 

[Bondjames]

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

 

[Bondjames]

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

 

[GriffMG]

@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 [Smile]ing

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
​

 

[Chris Miller]

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?

*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

 

[GriffMG]

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 [Smile]ing

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
​

 

[Chris Miller]

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

 

[GriffMG]

True

Regards

Griff
Keep [Smile]ing

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
​

 

[Chris Miller]

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

 

[AndrewMozley]

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

 

[Chris Miller]

Andrew,

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

Chriss

 

[Bondjames]

sorry sirs if for me there was misunderstanding

 

[mjcmkrsr]

Hi,

My suggestion

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

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