Formula in db extracting distinct values

[fomenter]

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

[DataBlender · Accepted solution]

Hi @fomenter,

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

[dsemitekol]

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]

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]

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 :)