Alteryx designer Discussions

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

Run workflows in sequence to use a control table for change data capture

Highlighted
Meteoroid

Hi All,

 

I'm trying to use a control table to load data to a DB2 table (Cannot use in-DB tools).

 

Here is what I want to do.

Eg: Worflow Name : CCE_MEASURE_VALUE_MORTALITY_INDEX.yxmd

 

Before running CCE_MEASURE_VALUE_MORTALITY_INDEX.yxmd to read records from the DB2 tables, I want to write a record to a control table.

 

Dhanushka_A_5-1579108952477.png

 

Next, in my CCE_MEASURE_VALUE_MORTALITY_INDEX.yxmd  workflow I have SQL statement in the input tool where I want to read

 

SELECT MSR.*,
ENCNTR.ENCNTR_ID
FROM APP_VIZIENT.V_EXPLR_MSR MSR
INNER JOIN APP_REPORTING.CCE_REPORTING_CNTL CNTL
ON CNTL.MSR_NM = MSR.MSR_NM
WHERE
DATE(MSR.MSR_DT) >=CNTL.LAST_LOAD_TMSTP

AND CNTL.STATUS_CODE='S'

 

After I extract these records and load the data to another DB2 table I want to update the record in the control table to look like this 

Dhanushka_A_4-1579108868749.png

 

 

I have created a macros to insert a record to the control table : 

APP_REPORTING_CNTL_start.yxmc

And another macro to update the record in the control table : 

APP_REPORTING_CNTL_end.yxmc

 

So here is the issue:

 

My workflow looks like this 

 

Dhanushka_A_6-1579109001942.png

 

The APP_REPORTING_CNTL_start.yxmc writes a record to the control table. See row 2 in the below screenshot

 

Dhanushka_A_7-1579109227861.png

 

 

However, the SQL query does not seem to pick up this record. Hence, it does not return any records. I have also tried using the Crewmacro List Runner but it did not seem to work as well. 

 

Any suggestions?

 

Thanks,

Dhanushka

@Dhanushka_A  can you post the workflow/macro package?

Labels