As with everything in Alteryx, multiple approaches are possible. Here's mine.
if isnull([STOP SERVICE]) then DateTimeadd([START SERVICE],1,"months") else [STOP SERVICE] ENDIF
Test it out, examine the outputs at various steps, make sure it's doing what you want. I tested it with success on your dataset, then I expanded your dataset, adding other members, and other months, all out of sequence. Output seemed correct to me.
The solution I am currently using is to create an automation script with Toad Datapoint to execute the script then execute the toad automation script in alteryx like a batch file. It just makes no sense that alteryx can't execute an sql. Seems like it should be really simple.
I would also like to know where the Data Product Knowledge Base is located, and how to access that info?
I understand that the data products are an additional fee option, but I don't understand why the knowledge base would be obscured from access.
Thanks in advance.
I apologize for the delay in getting a response to you.
Because of license agreements with our data vendors, only subscribers can gain access to data products documentation. If you would like to subscribe or find out more, we recommend getting in touch with your account representative.
While ConsumerView and D&B Calgary files are excellent databases for adding insight to customer files for analytics, they cannot be used for direct marketing purposes. That being said, Chick-fil-A had a track on Reporting, Analytics and Customer Loyalty at the 2016 Inspire Conference https://www.alteryx.com/inspire-2016-tracks which might be useful. I haven't check out the 2017 Tracks but here's the location: https://www.alteryx.com/inspire-2017-tracks.
Another area of the Community has use cases covering many topics: https://community.alteryx.com/t5/Alteryx-Use-Cases/tkb-p/use-cases.
Also check out the Data Bundle Documentation folders for details about the licensed datasets. Those are also found in the Data Products Knowledge Base https://community.alteryx.com/t5/Data-Products-Knowledge-Base/Q2-2017-US-Data-documentation/ta-p/76.... The EMS Targeting - ConsumerView Licensee Install User Guide.docx provides context for the ConsumerView fields.
Last but not least, check and see if there's a local Alteryx User Group to join. https://community.alteryx.com/t5/User-Groups/ct-p/usergroups. Rather than reinvent the wheel, someone might already have ideas to share and get you going in a good direction.
Hope this gets you started. Lonnie
Hi @John_S_Thompson ,
Access to the data product knowledge base is tied to your email/license key. If anyone else encounters this issue in the future you can email us at firstname.lastname@example.org and we will get you sorted out.
I'll check your access and follow up with you by PM, since you recently purchased the data bundle we likely need to add the access to your user name.
Let me know if you have any questions,
Try the following:
1. Append Tool: Use the price list as the Source and the list of orders + quantities as the Target input. This will append the price list to each record in the order table.
2. Formula to check if the order quantity is greater than the quantity in each record of the price table.
3. Filter to show only those values that are true (assuming that your price will be determined by the largest number that is equal to or less than the order quantity, so I think in your initial example that would be $49, not $47?)
4. Sample tool to select the Last 1 record, grouping by original order quantity.
Let me know if that helps!
Figured out the issue.
I was getting "invalid number" in my columns after using the select tool because I had duplicate headers in the file. Once I filtered out the duplicate headers and the NULL values. I then converted the data types using the select tool and lead that into the multi-field formula and it worked correctly.
Hey @becki. Try adding the attached macro to your workflow by downloading it and then right clicking on the canvas and selecting Insert-> Macro...
In the configuration, you can select 'Add Total Row Only' if you don't want a column total as well. You can also choose a label. The default is 'Total'. Hope this helps!
This is possible, you can try the workflow attached.
Though I appreciate your example may be a bit more complex than outlined with this dummy data please let me know if this does not work.
We start by summerizing as you mentioned, which as you said creates a new column. We can then union the data back in to make it a row.
I've also built a second example in the same workflow with subtotals for two categories.
Thanks for your question!
The tool you are looking for is called the 'Cross Tab' tool. Here is a very helpful 'Tool Mastery' article for this tool. Additionally, I've attached a workflow which uses this tool to achieve your use case using your sample data.
Hope this helps!
Pivoting and aggregating would solve your issue. Attached workflow.
Hey @HBarkdoll! First you can use a Formula tool to change the value in your Quarter column using:
[Quarter] + " Value"
From there, use a Cross Tab tool and group by Q_Category and Q_Description. Use Quarter as the new column headers and Value as your new column values. Hope this helps!