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

Alteryx designer Discussions

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

Exception Reporting

Asteroid

Hello,

 

I have a master data source that contains records from many different companies (~20).  I would like to set up some exception reporting on this master file.

 

For example, if Field 1 or Field 2 or Field 3 is empty, then give me that entire row of data, as it needs to be completed.

 

Once I have a list of all records with incomplete data, I want to filter it by company name.

 

Once I have the data separated by company names, I'd like to send an excel output of the incomplete data to the designated data steward for that particular company.

 

I know I can do this in a very tedious and manual fashion (ie. 20 filter tools, 20 output tools, 20 email tools with the output as attachment).

 

Is there a better way to do this?

 

As as aside - what if some companies have sub-companies, and I want to include the sub-companies on separate sheets within the excel output for that particular parent company?

 

Thanks in advance for any help!

Alteryx Partner

You should be able to do this with only a few tools (~5).

 

Can you supply some sample/dummy data ?

 

The key thing is that the Company name be included in the data and if the data steward email is in the data as well (or separate data) then it's 1 email tool as well :)

Asteroid

I am attaching a very basic example of mock data.

 

If Field 1 or Field 2 or Field 3 is empty, I would want the entire row of data to be added to the excel output for that company.  Multiple sub-companies within the same company should be included in the same excel output, but on a different sheet.

 

The outputs should be included as attachments (1 per email) to the designated steward for that company, with their email address included in the data.

 

I should note that some companies have multiple stewards, so multiple email addresses are required.  Could this be handled as well?  I am including an example in the mock data.

 

Thanks for your help!

Alteryx Partner

See attached :)

 

Check if any of the 3 fields are null, then create a file for the company, put the subcompany in a different sheet and email to the stewards.

 

The only thing I haven't really tested is the emailing out, but it should work :)

Asteroid

This is exactly what I'm looking for.

 

The only hiccup seems to be with the email tool, which errors out with "Error: Email (6): ComposerEmailInterface: Record#1 From Field is Empty"

 

This seems odd to me, because there are clearly two rows of data in the input to the tool.  Any ideas?

 

Thanks so much for your help thus far.

Asteroid

One other hiccup.  In the cases where multiple sheets are supposed to exist within the same file, the "overwrite file" selection is causing only the most recent sheet to be populated within that file.

 

Is there any way around this?

Asteroid

So after a bit more digging, I solved the Email error.  I (idiotically) forgot to populate the "From" address.  I thought the tool was trying to tell me that there were no records being fed into it (when there clearly were).

 

As such, my only issue now is the overwriting of the output files.  Any instance where I wanted multiple sheets for a single company results in the last written output to be the only sheet that exists within that file name.

Asteroid

Another update: it seems like I have solved my previous issue by switching the Output Option to "Overwrite Sheet".

 

This solves all existing problems; however, it creates another.  Depending on the run, I sometime get an error about a file being used by another process (obviously this occurs when a new sheet is trying to be written to a file that isn't quite finished writing its previous sheet).

 

It seems like a Block Until Done tool would be of use in this scenario, but I don't quite understand how to implement it properly (ie. without using filters to identify every case where multiple sheets are written to the same file).

 

Care to shed some lights?

Alteryx Partner

Do you have some sample data ? (even a list of company/subcompany codes) I added some more records to your data and I can't emulate the error.   I have seen this error before, but I haven't run across it for a while (I think the more recent versions of Alteryx handle it better).

 

Also, please consider marking one of the replies as a solution to this problem :)

Asteroid

I'd love to share the data but it's sensitive.  The error seems to happen at random points in each run.  I am using Designer 2018.2, and can't seem to get through a successful run without an error.

 

Is there some way to block writing to a sheet until the original has completed?

 

If not, I may have to just live with writing everything to its own file, and forget nesting the sheets within parent files.

 

Labels