Using the Visual Query Builder

[MattD]

Need more information on how to use our visual query builder when choosing tables or queries to input? We’ve put together a quick video in v10.1 for your reference:

https://www.youtube.com/watch?v=fJjAiAHtCuc

Find more information on our visual query builder below:

http://help.alteryx.com/current/index.htm#ChooseTableorSpecifyQuery.htm#Visual

[steven4320555]

  Hi Matt @MattD

I was doing some visual Query over an Exasol database with the Visual Query Builder. Generally, it is a very powerful tool, and taking advantage of the functionality of Active Query Builder https://www.activequerybuilder.com/hs15.html as described in the help document https://help.alteryx.com/current/index.htm#ChooseTableorSpecifyQuery.htm#Visual .

I have two questions emerged when using the tool, but I did not find answers in the community, hope this is a good place to get help.

My questions are:

1. According to the Active Query Builder information, there is a query tree view besides the Main, Grid and Table list views. https://www.activequerybuilder.com/hs120.html  Since the Visual Query Builder was introduced 2 years ago, is there any plan to update it and incorporate with more features, such as the query tree?

2. I find the linked object function is not working. Although I have defined primary key and foreign key in my Exasol database, only primary key is recognised by the query builder.  Is it because the database I am using not support the functionality? Or do I need to do something extra to get the linked object function working?  As the linked object suppose to be able to get related table according to the primary key and foreign key relationship. If the function can work properly, it will be easier to build the linkage of data sets, with less chance to make mistakes. 

Many thanks

Steven

[DavidP]

Hi @MattD, thank you for the video! I started playing with the VQB this week as part of the 2014.4 Beta testing program. Up to now, I've always just used the SQL Editor to construct my queries. I can see that the VQB would be really useful where the relationships between tables are nice and straightforward and where the WHERE clauses, etc. are nice and straightforward, but I wanted to put the VQB through its paces with some meatier SQL queries. I started by pasting an SQL query into the editor and then looked at how it is represented in the VQB to figure out if I'd be able to build it in the VQB. From what I can see, it would be very difficult to build a query like this in the VQB. Do you agree or am I missing something, i.e. is there a more intuitive way to put queries like this together in the VQB?

With uris As (
Select Distinct stasks.uri

From (Select Array_Agg(data.artefact.id) As ids,
data.artefact.uri From data.artefact

Where data.artefact.uri In (
Select a.uri

From data.artefact a Left Join task.task t
On t.id = a.id

Left Join service.expense_audit_process snp
On snp.image_qualification_task_id = a.id

Where (t.id Is Null Or snp.id Is Null) And a.external_id Like '%Linoma%') And data.artefact.expiration_date Is Null

Group By data.artefact.uri) As stasks

Where (

(Select COUNT(*)

From task.task t
Where t.id = Any(stasks.ids)) = 0)
Or (

(Select COUNT(*)

From service.expense_audit_process snp
Where snp.image_qualification_task_id = Any(stasks.ids)) = 0))

Select Distinct a.uri

From data.artefact a

Inner Join uris On a.uri = uris.uri

[hcampbe1]

I have an issue i've been experiencing when using VQB and it looks lik you have similar instance where your column names have an 'a.' added to the front.  I'm having to manually edit my queries in the free text (workaround) to strip every a. from column header in order for it to execute properly.  Does anyone in this community have an idea as to why and is there an alternate/difference ODBC driver that i should be using that will eliminate this behavior?

Here's an example of end of text from the SQL Editor: (additional fields here via Select.....) raw_sfdc_c_account.`a.global_hq_account_name__c` From raw_sfdc_c.account raw_sfdc_c_account Inner Join raw_sfdc_c.accountcodec raw_sfdc_c_accountcodec On raw_sfdc_c_account.`a.account_code_value__c` = raw_sfdc_c_accountcodec.`a.account_code_c`

When i connect with my db and pull in entire table no trouble AND if i strip all the 'a.' in front of column names i've had a bit of success in at least running the test query but need to spend more time fixing formatting.

[lepome]

@hcampbe1 Check out this article for info on the column names all having an a.

[simonaubert_bd]

Hello @MattD  @lepome 

Correct me if I am wrong, but the Active Query Builder component used in Visual Query Builder is totally outdated (version 2.10 versus 3.8). Can we expect an update soon? It would solve many connections issues, especially on ODBC API calls.

Best regards,

Simon

[SatyaSek]

visual query builder do not show the external tables created on amazon redshift, I am connecting using the ODBC string. I can query though. any suggestions on what is the solution?