Iif in a dbf file

Orongo123 · #1 (**permalink**) 12-06-12, 11:38

Hi

I have used the command WITH IIF for dbf-tables as

Code:

USE filename.dbf
ALTER TABLE filename.dbf
REPLACE ALL ColA WITH IIF(year>0,1,0)
COPY TO Result.dbf
CLOSE TABLES

But now I have several dbf-files so

Code:

...
REPLACE ALL ColA WITH IIF(((File1ColA + File2ColA) /File3ColA) >0,1,0)
...

I can create a new table with File1ColA, File2ColA and File3ColA and then do the WITH IIF. But is this really necessary?

jrbbldr · #2 (**permalink**) 12-06-12, 11:56

Code:

USE filename.dbf
ALTER TABLE filename.dbf
REPLACE ALL ColA WITH IIF(year>0,1,0)
COPY TO Result.dbf
CLOSE TABLES

The code you posted will:
1. Open (USE) one table
2. make the Replacement changes to the table field ColA
3. copy the entire table it to an external DBF file (Result.dbf)
4. and then close all tables
so at the end you should have NO tables open.

What do you mean now I have several dbf-files so...

Yes, you should have your original dbf file and you will have the one you copied that table to (result.dbf)

And what is it that you want to do.

But is this really necessary?

Not necessarily, but you haven't stated clearly and precisely what it is that you are needing to do and what you want as the end result.

Up until now all of your questions seemed to be focused on learning how to execute certain command IN GENERAL.

Good Luck

jrbbldr · #3 (**permalink**) 12-06-12, 12:26

If you wanted the new field (ColA) value in your original table (filename.dbf) you have it.

Referencing the code you have posted.....

You should NOT have needed to use the command ALTER TABLE filename.dbf at all since you are not changing the table STRUCTURE of filename.dbf

Your next line causes the ColA field values to be changed in the original table
You THEN copy that original table out to another External table (Result.dbf)

So at this point, you have 2 separate DBF files with the same contents (field values).

Let us know clearly and precisely what it is that you are after for an end result.
* Do you want to retain your original table values?
* Do you want the new values to appear in another, different table?
* Do you want the new values to appear in the same table, but in a new, different field?
* What?

Good Luck

Orongo123 · #4 (**permalink**) 12-06-12, 12:36

Hi
I want to take out three different columns from three different dbf-files. As the relation is
(File1Val1+File2Val2)/File3Val3, I need to confirm that File3Val3 =!0 (not equal zero). The use of IIF command have been with USE and addition of different values from different files have been using SELECT. So I don't really know when to use what (I do a lot of unnecessary ctrl+c and ctrl+v). The result from the equation will be used again in a later stage.

jrbbldr · #5 (**permalink**) 12-06-12, 13:39

Let address your issues one at a time....

"I want to take out three different columns from three different dbf-files."

You want to take out to where?
* To memory variables?
* To a different table?
* To where?

How many records worth of field values do you want to take out?
* All record's values?
* One or more specific record(s) values based on some specific selection criteria?
* What?

Now, for purposes of better mutual understanding, lets address your syntax usage.

File1Val1 OK I get it that the Value is from File1, but what Field within File1 is the value from?
* Maybe the value from File1.ColA where ColA would be the desired Field from which to get the value?
* Or something else?

NOTE - the syntax is Table dot Field (example: File1.ColA) to specify a specific Field within a Table
The OLD syntax used to be Table -> Field but that is no longer currently used.

So, with that in mind, there is NO File1Val1

File1 has one or more Fields (maybe named: ColA, Year, Qty, etc.) and those Fields each have an independent value.
Therefore you need to specify which Field Value(s) you want to use for the calculation.
Maybe, again for example, something like the value from File1.ColA with the value from File2.Year and the value from File3.Qty

Designating the File and the specific Field equates to that specific Field's value.
Therefore the syntax File1.ColC designates the value from Field ColC in File1

Also, in order to run a calculation of values from different tables there has to be something that can be used to match the individual records between the tables.

Think about 3 separate Excel worksheets
* For your calculation you might want to utilize the (1,2) cell value in WS1
* With the (2,5) cell value in WS2
* And the (5,3) cell value in WS3
So what tells the program what these 3 worksheets have in common so as to programatically 'know' which row (which Record) from each Worksheet (each Table/File) from which to get the specifically defined cell values?

So, for example, that the program can 'know' that Record1 from File1 matches something in Record3 from File2.
And therefore the program would use the ColA value from Record1 in File1 and the ColB value from Record3 in File2, etc.

With that analogy in mind, how are the 3 separate tables 'related'?
* Is there one Field within all 3 tables whose values are supposed to match in order to do the calculation?
* Or is there one Field in 2 of the tables and a different Field in the 3rd table that matches one of the other 2 tables?
* Or what?

Again, not knowing where you want these results to go, here is some sample code that might work for you...

Code:

USE File1 IN 0 EXCLUSIVE
SELECT File1
INDEX ON ColA TAG ColA

USE File2 IN 0 EXCLUSIVE
SELECT File2
INDEX ON ColA TAG ColA  && Assumption is that Field 'ColA' value matches in other File's

USE File3 IN 0 EXCLUSIVE
SELECT File3
INDEX ON ColA TAG ColA  && Assumption is that Field 'ColA' value matches in other File's

* --- Establish a Relationship between all 3 tables ---
* --- based on the ColA value matching in all 3 tables ---
SELECT File1
SET RELATION TO ColA INTO File2 ADDITIVE
SET RELATION TO ColA INTO File3 ADDITIVE

* --- Now the program 'knows' which records in the other tables ---
* --- from which to get the values ---
* --- And Put Results Back into File1 field 'ColA' ---
REPLACE ALL File1.ColA WITH ((File1.ColA+File2.ColB)/File3.ColC

Good Luck

Orongo123 · #6 (**permalink**) 12-06-12, 14:52

Hi again JRBBLDR, wow you're really good in explaining this. Sorry if my questions are unclear, all my questions are for the same program. Let me try to explain my program, the data (.dbf) look like

-------------------------------
TableFirst
-------------------------------
Year Col1 Col2 Col3
0 111 112 113
1 121 122 123
2 131 132 132
3 141 142 143
4 151 152 153

-------------------------------
TableSecond
-------------------------------
Year Col2 Col4 Col5
0 211 212 213
1 221 222 223
2 231 232 232
3 241 242 243
4 251 252 253

-------------------------------
TableShiftSecond
-------------------------------
Year Col2 Col4 Col5
0 201 012 013
1 201 022 023
2 201 032 032
3 201 042 043
4 201 052 053

So what I have two source tables, e.g. TableFirst.dbf and TableSecond.dbf. From the second table, one of the columns are shifted, here Col2 and named TableShiftSecond (you helped with this before, many thanks).
The shifted table are of generic format. The desired result are a summary of all three tables, but with a condition on one TabelFirst, that field Col3 only has values is year is between 1-3. This is a shorted version on my program, what I have are many steps of calculations before the final result. I hope this gives you a better picture of all my questions (ps. all fields are numeric, Year = #### and rest are doubles).

jrbbldr · #7 (**permalink**) 12-06-12, 17:10

Thank you for clarifying things somewhat.

So you have 3 tables, each with 4 fields (Year, Col1, Col2, & Col3)
And there are 5 records in each of the tables (Year = 0,1,2,3,4)

With a One-to-Many Table Relationship there is 'Parent' table and then one or more 'Child' tables.
And the 'Child' tables are 'related' to the 'Parent' table through a 'match' of the one or more field values.

Looking at the table examples you show, the only place that there is any possible match would be from the Year field in all of the tables since that is the only field in which common values exist in all of the tables and all of the other field values are so different.

Before we set up the Relationship, the 'Child' tables have to have an Index built on the Field that we are going to use to 'match'

Code:

* --- Build Index on Field 'Year' for use in Relation and set a reference 'Tag' name ---
USE TableSecond IN 0 EXCLUSIVE
SELECT TableSecond
INDEX ON Year TAG YearTag

* --- Build Index on Field 'Year' for use in Relation and set a reference 'Tag' name ---
USE TableShiftSecond IN 0 EXCLUSIVE
SELECT TableShiftSecond
INDEX ON Year TAG YearTag

Now we can use the table TableFirst as the 'Parent' table

Code:

SELECT TableSecond
SET ORDER TO YearTag

SELECT TableShiftSecond
SET ORDER TO YearTag

USE TableFirst IN 0
SELECT TableFirst
SET RELATION TO Year INTO TableSecond ADDITIVE
SET RELATION TO Year INTO TableShiftSecond ADDITIVE

* --- Now the application can 'see' through the 'related' records ---
* --- from the Parent table to each of the Child tables ---
* --- So we are set up to run our REPLACE algorithm on the Field Values ---

The desired result are a summary of all three tables
But Where is this summary to go?
* Back into one of the TableFirst fields?
* Into a totally different and new Result table?
* Where?

Good Luck

Orongo123 · #8 (**permalink**) 12-06-12, 17:18

The result are going into a new table. OMG you are awesome in explaining

jrbbldr · #9 (**permalink**) 12-06-12, 18:45

OK, before I get into the variety of approaches to a result, help me better understand your desired result

The desired result are a summary of all three tables, but with a condition on one TableFirst, that field Col3 only has values is year is between 1-3.

I guess that I can't visualize what you mean.
There is no instance where there is a TableFirst.Col3 value between 1-3
Therefore nothing would qualify to compute a value on.

Or are you indicating that we only want to calculate where the Field "YEAR" has values between 1-3 and exclude all other records?

Code:

* --- Using the Related Tables and a separate Recipient Table ---
* --- Relation between tables already in place from other code examples ---
cResultDBF = "C:\Temp\Result.DBF"  && Fully pathed Result Table Name

SELECT TableFirst
COPY FIELDS TableFirst.Year, TableFirst.ColA, TableSecond.ColB, TableShiftSecond.ColC;
   TO (cResultDBF);
   FOR BETWEEN(TableFirst.Year,1,3)
* --- The FOR clause will Exclude other records ---
* --- NOTE that the BETWEEN() function is a FP/VFP function ---
* --- It could be replaced by (TableFirst.Year > 0 AND TableFirst.Year < 4)
* --- Or another FP/VFP Function INLIST()    INLIST(TableFirst.Year,1,2,3) ---

USE (cResultDBF) IN 0 EXCLUSIVE
SELECT Result
ALTER TABLE Result ADD Result I  && Add field for computed value
REPLACE ALL Result WITH ((ColA + ColB)/ ColC)
* --- Since ONLY the qualifying Year records were written, you can calculate on ALL ---


* --- Alternative Approach using SQL Query ---
SELECT ((TableFirst.ColA+TableSecond.ColB)/TableShiftSecond.ColC) as Result;
   FROM TableFirst,TableSecond,TableShiftSecond;
   WHERE TableSecond.Year = TableFirst.Year;
   AND TableShiftSecond.Year = TableFirst.Year;
   AND BETWEEN(TableFirst.Year, 1, 3);
   INTO TABLE Result
* --- Again NOTE that the BETWEEN() function is a FP/VFP function ---
* --- It could be replaced by (TableFirst.Year > 0 AND TableFirst.Year < 4)
* --- Or another FP/VFP Function INLIST()    INLIST(TableFirst.Year,1,2,3) ---

SELECT Result

If we were to exclude the other records, should SOME value be represented or totally exclude those years from the resultant table?

Code:

* --- Using the Related Tables and a separate Recipient Table ---
* --- Relation between tables already in place from other code examples ---
cResultDBF = "C:\Temp\Result.DBF"  && Fully pathed Result Table Name

SELECT TableFirst
COPY FIELDS TableFirst.Year, TableFirst.ColA, TableSecond.ColB, TableShiftSecond.ColC;
   TO (cResultDBF)

USE (cResultDBF) IN 0 EXCLUSIVE
SELECT Result
ALTER TABLE Result ADD Result I  && Add field for computed value
REPLACE ALL Result WITH IIF(BETWEEN(Year,1,3), ((ColA + ColB)/ ColC), 0)
* --- NOTE that the BETWEEN() function is a FP/VFP function ---
* --- It could be replaced by (TableFirst.Year > 0 AND TableFirst.Year < 4)
* --- Or another FP/VFP Function INLIST()    INLIST(TableFirst.Year,1,2,3) ---


* --- Alternative Approach using SQL Query ---
* --- NOTE - Due to some 'quirks' with SQL Server, we need to initially put ---
* --- a 'dummy' value into the result records we want to be 0  ---
* --- to retain the REAL decimal values ---
SELECT IIF(BETWEEN(TableFirst.Year,1,3),;
      ((TableFirst.ColA+TableSecond.ColB)/TableShiftSecond.ColC),999.999) as Result;
   FROM TableFirst,TableSecond,TableShiftSecond;
   WHERE TableSecond.Year = TableFirst.Year;
   AND TableShiftSecond.Year = TableFirst.Year;
   INTO TABLE Result

* --- Now change those 'dummy' values back to 0 ---
SELECT Result
REPLACE ALL Result WITH 0 FOR Result = 999.999

Actually since you want the results to go into another totally separate table, I would probably lean towards the alternative SQL Query approach

You can see how I could easily be mis-understanding what you want.

Good Luck

Internet Services

Web Development

Digital Marketing

Consumer Tech