Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Formula in db extracting distinct values

fomenter
8 - Asteroid

Hi,

 

I'm trying to pull distinct values from a table in HIVE . First, I made the connection with 'connect In-DB'  (no problems here), then I used 'formula in db' to pull distinct values for name. 

 

I use ---- SELECT DISTINCT name FROM x_t1, but it seems to giving me an error. What am I doing wrong? x_t1 table is under a different folder if it makes any difference.

 

Thanks

4 REPLIES 4
DataBlender
11 - Bolide

Hi @fomenter,

 

I'd suggest entering the query directly into the connect in-db query box rather than the in-db formula tool.

dsemitekol
7 - Meteor

Hi,

 

Trying to accomplish the same thing.  However, I want to apply the "select distinct" halfway through my workflow, not at the begging.  I'm trying to use the In-DB Filter tool but am also pulling an error.  Any help on the syntax?

DataBlender
11 - Bolide

Hi @dsemitekol

 

Have you thought about using the summarize tool at that point (and grouping by the dimension you want the distinct values for)? If you're able to give some more info about the result you're trying to achieve I can probably advise further.

dsemitekol
7 - Meteor
Hi @DataBlender I think I found a workflow that accomplishes my task. I have transaction data by each day that also contains a unique customer number. My goal was to count only the first visit of the customer in the year and not include return visits. I ended up using the Summarize In-DB tool with Group By customer number and MIN of date. Then I rejoined the remaining attribute data back into the workflow. It was 2 extra tools (join and select to drop repeated attributes) but it looks like I got my result. My next task it to do the same, but on a month to month basis instead of the entire year. I was wondering if there was a slick formula that would accomplish the same task, but now it doesn't seem completely necessary. Building the workflow with the 2 extra tools was not that big of a deal :)
Labels