Cross Tab feature for In-DB tools

[shreyanshrathod]

Hi all,

I am using Alteryx Designer In-DB tools for a process.

I want to know how do I achieve In-DB CrossTab functionality?

Note :- I understand that I can use Data stream out and then use standard CrossTab for it, but bringing the data into Alteryx just for a Cross Tab is not feasible as the data size is huge.

Any help on the query?

Thanks in advance.

[pedrodrfaria]

Hi @shreyanshrathod 

Depending on what you need to do, you can create a logic with the Summarize in DB, Filter in DB and Select in DB.

You also have the Transpose in DB to use to try to get you closer to a Cross Tab in DB. The Transpose in DB is located under the Laboratory category.

[shreyanshrathod]

Hi @pedrodrfaria ,

Thank you for sharing your thoughts on this!!

Is there a way we can run Custom SQL scripts at any point in the workflow?

The thought being at a point where I need CrossTab, I can use Database script to do that and do other operations in Alteryx workflow.

[pedrodrfaria]

Hi @shreyanshrathod 

You should be able to do this with the Connect in DB tool

[jrpaul]

@pedrodrfaria   Hi, could you please describe how to simulate an inDB crosstab using Summarize, Select and Filter inDB tools (or Transpose if that can work) and/or post a template workflow?   

I have a  data table with  Record ID (string), Group Name (string), and Value (1 = True).   A record ID may belong to multiple groups (so can have multiple rows with same ID).   I want to pivot it so each row has one Record ID, and then column names for each Group Name, and cells are either 1 or null.  Creating flags to indicate the groups a record belongs to.    Currently using Crosstab but have to stream the data out of inDB to do that.   

Thanks,

Jeff

Sample Data:

ID    Group     Value

1         A             1

1         B             1

2         A              1

2         C             1

3         C             1

Want:

ID      A      B     C 

1       1       1    null

2      1      null   1

3     null   null     1  

[jrpaul]

I did come up with a method that I think will work, but curious if there's a more automated way.   

I can add a formula tool that has a calculation for each column I want (e.g. "A", "B", "C"...), with the calculation checking the Group value for that letter.  e.g. if Group = "A" then the "A" calc = 1 otherwise 0.  Same for each of the other groups/letters. 

Then I used a Summarize tool to Group By Record ID and used "Max" on each group name (A, B, C) to get the data table into one record per row with a column for each group and 0 or 1 as the flag values.  

Downside is if the source data contains new group names, I have to manually add a calculation for it and add to the Summarize tool or it will not have a flag in the final output table.  This appears to work, but if there's a way to avoid manually updating those two tools, would love to see it!  

[simonaubert_bd]

Hello. Please note you can vote for this idea : https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Cross-Tab-in-DB/idi-p/161508