Find which 'values' are still missing.

[Dannygast]

I did some googling, but could not find an answer, so I hope some of you can help me out.

 

Here's the thing:

 

I have 2 tables with a relation.

Table 1, 'BOX' has the field 'Box_Color' with 3 records: Red, Blue and Green.

Table 2, 'CONTENT' has the relational field 'Box_Color' and the field 'NumberInBox'. there are several records with different numbers (let's say 1-9) in the different colored Boxes.

 

Now in the end, I have 3 Boxes with Numbers in it for example:

 

Red: 1, 3, 3, 5 and 8

Blue: 3, 2, 8 and 4

Green: 5, 4, 7, 8, 2, 2 and 1

 

Now my Question:

How can I list/find the missing numbers (in the list: 1-9)

So the answer should be like:

 

Red Missing: 2, 4, 6, 7, 9

Blue Missing: 1, 5, 6, 7, 9

Green Missing: 3, 6, 9

 

What would be even more nice, count how many each number occurs, and include the 0's

So:

___Red_Blue_Green

1|....1......0......1

2|....0......1......2

3|....2......1......0

4|....0......1......1

5|....1......0......1

6|....0......0......0

7|....0......0......1

8|....1......1......1

9|....0......0......0

 

 

I hope I made myself clear as my English may be bad ;-)

 

Thnks in advance.

[cchumi]

Hi

I hope I understand your need.

But why not use patterncount to compare your box list with your comparison list.

If the result is 0 then it not exist so you could get the non existing comparison number. Do It in a loop. I use this a lot of time.

 

Is this what you need ?

[Maarten Witberg]

For enumerating the missing values, try this:

 

1. create a field in BOX, call it PersistentZero [calculation, number resuilt, stored] = 0

 

2. create a second TO between BOX and CONTENT , using PersistentZero and the NumberInBox field in Content AND Box_Color, call it "CONTENT | noughts" (or whatever)

 

You can now count and enumerate

 

CountNoughts [calc, number result, unstored] = Count ( CONTENT | noughts::ID )

 

EnumerateMissing = Substitute ( List ( CONTENT | noughts::SequenceNumber ) ; ¶ ; ", " )

 

 

 

For getting the total of each color, you can use

 

CountColors [calc, number result, unstored] = Sum ( CONTENT::NumberInBox )

 

 

Hope this helps

Edited December 17, 2016 by Maarten Witberg

[Dannygast]

2. create a second TO between BOX and CONTENT , using PersistentZero and the NumberInBox field in Content AND Box_Color, call it "CONTENT | noughts" (or whatever)

 

 

Could you please explain to me what you mean with 'TO', is this a second relation?

 

 

I found out TO stands for Table Occurrences, But still Step 2 Puzzles me...

 

I need a 3rd Table "CONTENT | Noughts"?

[Maarten Witberg]

"CONTENT | noughts" is a table occurrence of the table CONTENT, so no, there still are only two tables (CONTENT and BOX)