* censuses - query with different values for an output column

[ississi]

Hi,
So I've got a query which shows a list of people and what years they have censuses for, for each of the census year, with a 'Y' for each cell they have one. What I'd like to do, is also show which years they shouldn't have a census for - ie before born and after death, if I have that information. So, using a made-up language to show what I mean:

IF data(census-year) OUTPUT "Y"
ELSE IF (census-date < birth-date) OR (census-date > death-date) OUTPUT "-"

I've got the first line, but not the second.
Is this do-able? If so, any clues, please?

Many thanks

1.jpg (67.52 KiB) Viewed 2322 times

2.jpg (73.14 KiB) Viewed 2322 times

[tatewise]

Yes, it is definitely possible. See FHUG KB Download Kinsfolk Census To Do Table.
It complicates things slightly by checking Census (family) events as well as Individual Census events.

The ATTACHMENT Kinsfolk Census To Do Table goes further by using Estimated Birth & Death dates when actual dates are not available.

BTW: In your screenshot, some Expressions for 1851 and 1871 use [year=11] which is a typo error.

[ississi]

Thanks so much Mike, that's great - and thanks for spotting the typos - oops!

[ississi]

I've noticed that 1921 is included in the Columns but not in the Rows,
I think (if I've got it right)

Code: Select all

=IsTrue((EstimatedBirthDate(%INDI%,EARLIEST,2) > DateAt(1921,6,19)) or (EstimatedDeathDate(%INDI%,LATEST,2) < DateAt(1921,6,19)) or (%INDI.CENS[year=1921].DATE% = DateAt(1921,6,19)) or (%INDI.FAMS>CENS[year=1921].DATE% = DateAt(1921,6,19)) or (%INDI.FAMS[2]>CENS[year=1921].DATE% = DateAt(1921,6,19)) or (%INDI.FAMS[3]>CENS[year=1921].DATE% = DateAt(1921,6,19)) or (%INDI.FAMS[4]>CENS[year=1921].DATE% = DateAt(1921,6,19)))

needs adding to the Rows of the Kinfolk Census To Do Table (mind-blowing query, by the way )

[ississi]

...and 1921 made visible in the Columns

[tatewise]

I probably last updated that Query before the 1921 Census was available and made some preparations for it.
What you have added is perfectly correct.

[Gowermick]

There is a CensusMissingAges query downloadable from the knowledgebase, which I have modified and which gives a result similar to what you're after.

For each census year, it gives the following entries for each individual:
1. x means not relevant, individual was not alive on census date
2. Blank, individual has a census entry, but age has not been entered
3. Age, age as appears on census
4. find, Alive on census day, but haven't yet found their census entry

The purpose of the query was
a) to find all census entries where I had omitted entering their age from the census.
b) to indicate census entries I still need to find.

see image

Censusmissingages.jpg (151.45 KiB) Viewed 2243 times

The formula I used for each column is as follows (adjust for different census years)

=TextIf((%INDI.BIRT.DATE% > DateAt(1841,0,0)) or (EstimatedDeathDate(%INDI%,MID,2) < DateAt(1841,0,0)),"x",TextIf(Exists(%INDI.CENS[year=1841].DATE:YEAR%),%INDI.CENS[year=1841].AGE%," find "))

[ississi]

Thanks Mike L, I'll give that a look

[tatewise]

Interesting!
Why do you choose to use only explicit Birth Dates instead of Estimated, but do use Estimated Death Dates?
Have you considered the following more compact function instead of the nested TextIf(...) function:
CombineText( , %INDI.CENS[year=1841].AGE%, , " find " )
Also, by using DateAt(1841,0,0) you may be excluding/including some people by mistake.

[ississi]

Hi Mike L,
I've had a look but can't find where to download CensusMissingAges, could you tell me where to find it, please?
Many thanks

[Gowermick]

Hi Mike L,
I've had a look but can't find where to download CensusMissingAges, could you tell me where to find it, please?
Many thanks

Try https://fhug.org.uk/kb/download/ages-on-census-3/

[Gowermick]

Interesting!
Why do you choose to use only explicit Birth Dates instead of Estimated, but do use Estimated Death Dates?
Have you considered the following more compact function instead of the nested TextIf(...) function:
CombineText( , %INDI.CENS[year=1841].AGE%, , " find " )
Also, by using DateAt(1841,0,0) you may be excluding/including some people by mistake.

I was just experimenting with the original query (https://fhug.org.uk/kb/download/ages-on-census-3/).
i didn't design it per se, it just evolved. If it ain't broke, don't fix it.

It did what I wanted and didn't seem to miss anyone, so I left well alone

Now if only we could get colour into the reultset

[tatewise]

I've had a look but can't find where to download CensusMissingAges, could you tell me where to find it, please?

This is an area where the Knowledge Base Downloads need improving because the title of the article often bears little resemblance to the name of the download file!

[Gowermick]

Miketate,
I agree, I even had trouble finding it again
I resorted to my browser history to find where I got it from. I really couldn’t recall what search term I had used!

BTW is there somewhere that lists ALL queries available for download?

[tatewise]

BTW is there somewhere that lists ALL queries available for download?

FHUG > Knowledge Base > Downloads/Plugins > Downloads > Queries > All.
However, that lists the article titles rather than the download filenames.

[Gowermick]

Ok, but why are they listed with plugins? That just confuses people (i.e me ), who don’t associate queries with plugins!

Makes more sense to list them as FHUG > Knowledge Base > Downloads/Queries

[ColeValleyGirl]

But Queries ARE Downloads, Mike. We can't enumerate every type of download on the menu. It would get a bit long

Pluginds ar elisted separately as the link takes you away from the KB to the Plugin Store.

[Gowermick]

But Queries ARE Downloads, Mike. We can't enumerate every type of download on the menu. It would get a bit long

Pluginds ar elisted separately as the link takes you away from the KB to the Plugin Store.

I know what you mean, but why is it headed Downloads/Plugins?

Why not just Downloads? This would cover Queries and Plugins etc. It is the /Plugins in the title that I find confusing

[ColeValleyGirl]

Mike

That menu item has a link to the plugin store under it, so I'm not sure why you find it confusing that it says so. Or are you saying you think of them as the same thing, technically we cant do anything other than link offsite for them - there's no way to list them all under the downloads page.

(It also has link to code snippets for plugin authors but those are a minority interest so arent shown in the 'headline'.)

[BillH]

Would it be less confusing if it said "Downloads & Plugins" rather than "Downloads/Plugins/?

Just a thought... I have no problems with the way it is.

Bill

[tatewise]

I suspect Mike's point is that Plugins are Downloads just as much as Queries, Diagram Types, Fact Sets, etc.
So why highlight Plugins in the Menu name but not Queries, etc.

Suggestion:
In the Menu just have a Downloads option that opens a Downloads page similar to the current page.
In addition to the existing 10 sub-headings, add a sub-heading for Plugins and another for Plugin Snippets.
Those can then refer to the Plugin Store and Snippets respectively.

[ColeValleyGirl]

Mike, that would be a huge redesign that made the underlying architecture much more complex.

[Gowermick]

Helen,
All I’m suggesting is that top level says Downloads, rather than Downloads/Plugins! It is the word Plugins in the title that puts me off. The rest of the menu structure is fine, and doesn’t need changing.

[ColeValleyGirl]

Then how would the people who dont think plugins and downloads are the same thing know where to look for plugins?

And what menu structure would you have beneath Downloads? The same as now (wouldnt that be weird?). Or would you use a different word for downloads (confusing)

[Gowermick]

Helen,
If people don’t think downloads and plugins are the same, how do they expect to get plugins without downloading them? Carrier pigeon?