-1

I am trying to generate a table for the Gantt chart. Table should have this format: https://developers.google.com/chart/interactive/docs/gallery/ganttchart#data-format

So,I need task name the same like taks ID, but in Query I can't use Col1 twice (I get error)

=QUERY({Tab1;Tab1};"select Col1,Col1,Col5,Col16,Col17 WHERE Col16>now() ORDER BY Col5 DESC,Col17 ";0)

The second point is that it is also not possible to merge two columns as a result, so it doesn't work:

=QUERY({Tab1;Tab1};"select Col1+Col7,Col1,Col5,Col16,Col17 WHERE Col16>now() ORDER BY Col5 DESC,Col17 ";0)

Here is my data and 2 results what I neet to get by QUERY https://docs.google.com/spreadsheets/d/1CZYgfYo6oIeONZOH6ZR5rOW615HuH4ICaoe7lj0dapw/edit#gid=0

These are such trivial things in a real SQL, is there no way to do it somehow straightforwardly in Google Query? So far I have found a combination of QUERY and ARRAYFORMULA but then there are very complicated queries - mutants. Not easier?

Grzegorz Mogilewski
  • 932
  • 6
  • 8
Peter222
  • 154
  • 1
  • 16

2 Answers2

1

You don't need Query, just Arrays. You will get the first result from this code:

={ARRAYFORMULA(B3:B&" "&C3:C)\A3:A}

The second result from this code:

={A3:A\A3:A\B3:B1}

Based on your example I assume that you are not using US spreadsheet settings. If so formulas have to be change to:

First: 

={ARRAYFORMULA(B3:B&" "&C3:C),A3:A}

Second: 

={A3:A,A3:A,B3:B}

Link to working example: https://docs.google.com/spreadsheets/d/1eMkOkyFwvDeYSy-8UlhQum4OWcb-4WJqGxy_CXM8pVs/edit?usp=sharing

I see that in your real sheet you would like to compare some data with now(). You can easily do this using array I propose as a source to Query. There will you have something like this (of course now it will not work - its only an example - an array have only 2 columns, not 15):

=QUERY({ARRAYFORMULA(B3:B10&" "&C3:C10)\A3:A10};"select * where Col15>now()";0)

About Query - you can't perform arthmetic operations on column containing strings. Look at the documentation: https://developers.google.com/chart/interactive/docs/querylanguage#arithmetic-operators

Grzegorz Mogilewski
  • 932
  • 6
  • 8
  • Thank You for quick answer... I gave a simple example, but in fact my case is more complex (conditions and sorting), that's why I insisted on QUERY which seems more natural here. I know that it is not possible to use the + sign to combine a text, but neither does CONCATENATE, nor &, I wanted to show the lack of any way to combine a text using QUERY. It seems to me that there will actually remain the use of ARRAYFOLMULA in combination with the FILTER... but then the question remains why QUERY was created when it lacks basic possibilities? I hope that Google will develop this in the future. – Peter222 Aug 22 '18 at 05:31
  • Sorry, I missed the last part of the answer using QUERY and ARRAYFOLMULA as source, it looks like it will be the best combination - thank you again! – Peter222 Aug 22 '18 at 05:34
0

"I can't use Col1 twice (I get error)"
You can duplicate your indata that to solve this.

QUERY({Tab1 Column 1\Tab1 Column 1};"Select Col1, Col2......"

"Tab1 Column 1" is now Col1 and Col2

"The second point is that it is also not possible to merge two columns as a result, so it doesn't work:"

Yes, adding result of column is possible "select Col1+Col7......" is correct.

user10228838
  • 26
  • I do not think I have specified the subject in detail, I will give you the best example. Here is my data and 2 results what I neet to get by QUERY: https://docs.google.com/spreadsheets/d/1CZYgfYo6oIeONZOH6ZR5rOW615HuH4ICaoe7lj0dapw/edit#gid=0 – Peter222 Aug 21 '18 at 19:11