-1

I'm working with a BigQuery database table that holds history changes to different fields as separate events in a history table.

For example, take a table that has two fields:

Id Food Color
6 Bacon Red
9 Salad Green

Any changes to the values in this table would be output to a history table like:

History table

Change date id Field name Field Value
17 Jan 2022 6 Food Steak
19 Jan 2022 9 Color Yellow
21 Jan 2022 6 Color Blue

How can I transform the 'history' table so that it flattens the change values into a single inherited view of change state as shown below?

Transformed table

id Date Food Color
6 17 Jan 2022 Steak Red
9 19 Jan 2022 Salad Yellow
6 21 jan 2022 Steak Blue
June7
  • 19,874
  • 8
  • 24
  • 34
user2868835
  • 1,250
  • 3
  • 19
  • 33
  • Output does not reconcile with data. There is no Salad and Red in the sample so why are they in output? Why would Steak associate with Blue since they don't have same date? How many possible FieldName values? – June7 Feb 10 '22 at 08:34
  • The process assumes that a person has changed the values in the core table, and that a record of the changes is output to the history table. For example, on the 17 Jan a person changed the 'Food' field value of record #6 from 'Bacon' to 'Steak' – user2868835 Feb 10 '22 at 08:45
  • You want to associate changed value with current data from other fields? So the current data no longer has Bacon and Green values? – June7 Feb 10 '22 at 09:31
  • If my answer addressed your question, please consider accepting and upvoting it. If not, let me know so that I can improve my answer. – Shipra Sarkar Feb 15 '22 at 04:43
  • If record 6 Color was changed to Blue, why would your output show Red? Most history or audit tracking utilities I've seen would record old value. http://allenbrowne.com/AppAuditCode.html – June7 Feb 20 '22 at 10:19

2 Answers2

0

This requires to first PIVOT history table data then join to current data table. I use Access SQL but maybe can adapt the following:

Query1:

TRANSFORM First(History.FieldValue) AS FirstOfFieldValue
SELECT History.ChangeDate, History.ID
FROM History
GROUP BY History.ChangeDate, History.ID
PIVOT History.FieldName;

Query2:

SELECT Data.ID, Query1.ChangeDate, 
    IIf([Query1].[Color] IS NULL, [Data].[Color], [Query1].[Color]) AS C, 
    IIf([Query1].[Food] IS NULL,[Data].[Food], [Query1].[Food]) AS F
FROM Query1 INNER JOIN Data ON Query1.ID = Data.ID;
June7
  • 19,874
  • 8
  • 24
  • 34
  • I need a BigQuery based solution – user2868835 Feb 10 '22 at 12:50
  • I did say 'adapt'. Explore PIVOT in BigQuery. https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-bigquery. Make an attempt, edit question to show. Questions with no effort to solve usually just get closed. At least I gave you something to explore. – June7 Feb 10 '22 at 19:28
0

As per your requirement, you can use below code to get expected output.

SELECT h.id,h.DateofChange,
       MAX(IF(Field_name = "Food", Field_value, d.Food)) as Food ,
       MAX(IF(Field_name = "Color", Field_value, d.Color)) as Color 
FROM `project.dataset.history3` as h, `project.dataset.demo3` as d WHERE h.id=d.id
GROUP BY h.id,h.DateofChange

I am attaching screenshots of the result.

Demo Table

enter image description here

History Table

enter image description here

Query Results

enter image description here

June7
  • 19,874
  • 8
  • 24
  • 34
Shipra Sarkar
  • 1,385
  • 3
  • 10
  • This emulates PIVOT. If there really is only two fields (Food and Color) then this works nicely. If there are many more fields involved, it gets cumbersome. – June7 Feb 20 '22 at 10:13