New Encounters ETL - Superset

[csc5k]

Creating a ticket to track the new extract of encounters we're loading into Superset. Each row in the extract should be a unique encounter. The extract should include the following columns:

• encounter_id - Encounter ID
• encounter_date - Encounter Date
• zeir_id - ZEIR ID
• Gender (male/female) - do we want to do male 1 and female 1?
• date of birth
• Mother id
• Child HIV exposure
• facility_id
• facility name
• district id
• district name
• province_name
• province_id
• provider id
• provider name
• child weight
• 1/NULL for each of the following vaccines/encounter types:
  • BCG 1
  • OPV 0
  • OPV 1
  • PCV 1
  • Penta 1
  • Rota 1
  • OPV 2
  • PCV 2
  • Penta 2
  • Rota 2
  • OPV 3
  • PCV 3
  • Penta 3
  • Measles 1
  • MR 1
  • OPV 4
  • Measles 2
  • MR 2
  • BCG 2
  • vitamin A - not a vaccine, but an encounter
  • mebendezol (anti-worm) - not a vaccine, but an encounter
  • child weighed? - not a vaccine, but an encounter

At a later date, we will look to expand the encounter info to determine whether a vaccine was given on time or late

[mberg]

Clay - can we add a slug and label for each one.

Also let's group some of these. Ie) child_weighed - true/false and child_weight - 8.4 kg

[AndreLesa]

Let's add the province name in there too.

[mberg]

Feel free to edit the thing above

…

On Thu, Nov 2, 2017 at 10:28 AM, Andre Lesa ***@***.***> wrote: Let's add the province name in there too. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#11 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AADiQaEcG7d5rTn7XqjGaNAZpJyCs0Lyks5sydGcgaJpZM4QN7uD> .

[csc5k]

I'm putting together a spreadsheet with data types etc. for each item. I'll link it in the GitHub doc and am including the edits you guys suggested so far. Will update when done (shortly)

…

On Thu, Nov 2, 2017 at 5:38 PM, Matt Berg ***@***.***> wrote: Feel free to edit the thing above On Thu, Nov 2, 2017 at 10:28 AM, Andre Lesa ***@***.***> wrote: > Let's add the province name in there too. > > — > You are receiving this because you commented. > Reply to this email directly, view it on GitHub > <#11 issuecomment-341438858>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/ AADiQaEcG7d5rTn7XqjGaNAZpJyCs0Lyks5sydGcgaJpZM4QN7uD> > . > — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#11 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/Afh_uOsvVQvfePN4GJx8LsYQe2eEqB18ks5sydPmgaJpZM4QN7uD> .

[csc5k]

See the attached spreadsheet for a list of data elements incl slug/column header, readable name, data type, and detail.

Currently we're including both ID and name of things like provider, district, facility, etc. - are the IDs necessary or should we just focus on name?

https://docs.google.com/a/ona.io/spreadsheets/d/1qwNzQRsABqB7orYblruTqNUBo8a221pVYas1s5cYxH4/edit?usp=sharing

ZEIR Encounter ETL Spec.xlsx

[AndreLesa]

ID not necessary actually. I don't see a use for them. Unless on the export side if someone wants to see an export, but even then.

…

On Thu, Nov 2, 2017 at 4:44 PM claycrosby ***@***.***> wrote: See the attached spreadsheet for a list of data elements incl slug/column header, readable name, data type, and detail. Currently we're including both ID and name of things like provider, district, facility, etc. - are the IDs necessary or should we just focus on name? https://docs.google.com/a/ona.io/spreadsheets/d/1qwNzQRsABqB7orYblruTqNUBo8a221pVYas1s5cYxH4/edit?usp=sharing ZEIR Encounter ETL Spec.xlsx <https://github.com/OpenSRP/path-zambia-etl/files/1438172/ZEIR.Encounter.ETL.Spec.xlsx> — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#11 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADd6g0cjgO4bN4nLOUNN5gBkUhYEf_KXks5sydVbgaJpZM4QN7uD> .

[csc5k]

OK cool. To be clear, I removed the following (updated version also linked and attached)

facility ID
district ID
province ID
provider ID

https://docs.google.com/a/ona.io/spreadsheets/d/1qwNzQRsABqB7orYblruTqNUBo8a221pVYas1s5cYxH4/edit?usp=sharing

ZEIR Encounter ETL Spec (1).xlsx

[csc5k]

Just pinging @mberg @AndreLesa to see if they have further feedback on the specification for the encounters ETL.

Note that in order to calculate the Dropout rate, we're going to create a separate ETL that transmits aggregate data for each date. This ETL is documented here: #12

This is due to the way line graphs in Superset aggregate numbers. I can explain in more detail if there are questions.

[csc5k]

Reviewed output with @AndreLesa and @manutarus . We're going to update "child HIV exposure" column to return a YES/NO/NULL instead of POSITIVE/NEGATIVE/NULL

The column in Postgres will have the header "child_hiv_exposed"