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

[JAKupchik]

I'm currently wrestling with this one myself, and am not ruling out the manipulation of the XML view to accomplish this.  That or 'hard-coding' a SQL expression to accomplish this.  If one is able to dynamically create a field based on field values, one would just need to make an iterative macro to loop through it for as many field values exist.  Or something to that effect.  Good luck!