community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Historical Data Storage

Some times, when we have historical data from a data source and we want storage in a database, have some problems

 

  1. Update, insert is soo slow.
  2. Unnecessary data is stored.
  3. The records for the same ID, are the same for consecutive days.


For this reason, a solution at third the problem is a Macro that joins the days with the same information.

 

Example of the problem.

 

Capture_problema.PNGData Sample

The records 1 and 2 have the same information, we can transform the table to delete the second record? 

 

In this post, we show a solution to this problem, for this, we use an iterative macro.

 

  • Firstly, we must order the table by ID, and Date, in ascending order both. It is because we have more that one ID, and for have all record with the same ID together and consecutive, we must do it. The last date is the last register for this ID.

 

  • The next step is to add the field Started date and End date, for this using ensure tool of Crew Macros. In the first iteration the macro adds this field, and for the rest iterations, this fields existing.

 

  • Then, we count the number of the register where the Started date is null, in the first iteration, every register has null this field. The next iteration, less register have this field null, and finally, no record has this field null, this is for the final iteration. In the final iteration count is 0 and all records go to output tool macro. For the final iteration count = 0 for others count != 0.
  • Capture_final_iteration.PNGFinal Iteration Condition

 

  • Now we selected the oldest date from the table. As the table is in order, we can do it selected the first register and his date. Then append this date to all record of the table and select the record that has the field data equal to the oldest date.

 

Capture_fecha_primera.PNGThe oldest Date

  • If the data is different than the oldest date, the record goes to output tool macro iterative, it is the output than come back to input macro to the next iteration.

 

  • Now, started the most important part of the workflow, it compares the records. First, we use join tool to join the record than having started date, not null (the record modified in other iterations) and the records with the oldest date.

 

  1. The L is the record in the previous iterations that don't appear in this iteration
  2. The J the record appeared in the previous iterations and have the record for the oldest date, have the same ID.
  3. The R is the new records for this iterations.

Capture_news.PNGJoin Records

 

  • But for comparing records, the records of the previous iteration and the new records for this iteration (for the same ID), we want this record in a different row, for this reason, the output j to the join tool is not a correct output, for this reason we join the R and L output of the join tool with his origin, and view the records that don't are in the J output of the join. This records is in the J join output of the previous tool, and are in a different row, then we only must union.

 

Capture_j_join.PNGJ output in different rows

  • We join both r output of the join tools.

 

Capture_union_j_join.PNGUnion R output join tools

  • After the union, we sort the record by ID and Date, the same than at the beginning of the workflow. Now we select the records, remove the field Date, apply the tools unique, and summarise for ID and count the record. This is because, if the records with the same ID, if we remove the date, if have the same value for all field (less start date, end date) then only have a record with this id in u output of Unique tool, if not have 2 records for the same ID.

Capture_unique.PNGUnique tool

  • Now if the count of Unique records is 1 and Started date is not null, then we change count field to 2. It is for filter the new record with having the same values of the records of previous iterations. We filter the records that value of count not equal to 2 and sort the table. 

 

  • Now we must update the values of started date and end date because the record that is in this part have changes in the values. 

 

  • First, if the record has Started date not null, then is a record of a previous iteration, then his End date value is the value of Data field with the same ID, it is ordered previously, then End data = row + 1: Date. For the record that Started Date field is null, the value of this field is the value os his date. 

 

Capture_records.PNGUpdate Fields

 

  • Now we union the thread of the last iteration and the thread with the records with the field End Date not null, this records is not useful for the next iterations. 

 

  • The records with End Date field null will union to the other records with started date null, and pass to next iteration.

 

Capture_output_macro.PNGEnd of iteration

I hope that it is useful for yours, attach the workflow.

 

If need more details let me Know.

 

Thx!!!

An example of the result.

 

Capture_result.PNGFinal Output

Labels