Search code examples
google-sheetsgoogle-sheets-query

Google Sheets How to sort this query by dates?

I have this query, that brings the information from some tabs

=QUERY({
IFERROR(QUERY(General!A2:P,"select 'General',K,G,H,I,J where not G contains 'N/A' label 'General''' ",0),{"","","","","",""});
IFERROR(QUERY(Salud!A2:P,"select 'Salud',K,G,H,I,J where not G contains 'N/A' label 'Salud''' ",0),{"","","","","",""});
IFERROR(QUERY(Parejas!A2:P,"select 'Parejas',K,G,H,I,J where not G contains 'N/A' label 'Parejas''' ",0),{"","","","","",""});
IFERROR(QUERY('Paz Hogar'!A2:P,"select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar''' ",0),{"","","","","",""});
IFERROR(QUERY(Sustento!A2:P,"select 'Sustento',K,G,H,I,J where not G contains 'N/A' label 'Sustento''' ",0),{"","","","","",""});
IFERROR(QUERY(Hijos!A2:P,"select 'Hijos',K,G,H,I,J where not G contains 'N/A' label 'Hijos''' ",0),{"","","","","",""});
IFERROR(QUERY(Educacion!A2:P,"select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion''' ",0),{"","","","","",""});
IFERROR(QUERY(Otros!A2:P,"select 'Otros',K,G,H,I,J where not G contains 'N/A' label 'Otros''' ",0),{"","","","","",""});
IFERROR(QUERY(NA!A2:P,"select 'NA',K,G,H,I,J where not G contains 'N/A' label 'NA''' ",0),{"","","","","",""});
IFERROR(QUERY(a!A2:P,"select 'a',K,G,H,I,J where not G contains 'N/A' label 'a''' ",0),{"","","","","",""})},
"select * where Col1 is not null and not Col1 contains 'NA' order by Col3 ",0)

My problem is that is not sorting by dates correctly

enter image description here

How can I accomplish this?

Here is a demo sheet

Edit: The updated formula works perfect now, But im trying to add one more column (E) to the query, and when I add it, im getting error, no Col7 How can I add Col E in the 4th position of the formula ? I have been hours, with no luck ;(

 =ARRAYFORMULA(SUBSTITUTE(QUERY(QUERY({QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where E <> '-'  label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where E <> '-'  label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where E <> '-'  label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where E <> '-'  label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where E <> '-'  label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where E <> '-'  label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where E <> '-'  label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where E <> '-'  label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where E <> '-'  label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where E <> '-'  label 'a'''",         0), {"","","","","",""})},
 "where Col1 is not null and not Col1 contains 'NA'", 0),
 ARRAYFORMULA(DATE(20&RIGHT(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where E <> '-'  label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where E <> '-'  label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where E <> '-'  label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where E <> '-'  label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where E <> '-'  label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where E <> '-'  label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where E <> '-'  label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where E <> '-'  label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where E <> '-'  label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where E <> '-'  label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0)
, 2), MONTH(MID(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where E <> '-'  label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where E <> '-'  label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where E <> '-'  label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where E <> '-'  label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where E <> '-'  label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where E <> '-'  label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where E <> '-'  label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where E <> '-'  label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where E <> '-'  label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where E <> '-'  label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 4, 3)&1), LEFT(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where E <> '-'  label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where E <> '-'  label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where E <> '-'  label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where E <> '-'  label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where E <> '-'  label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where E <> '-'  label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where E <> '-'  label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where E <> '-'  label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where E <> '-'  label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where E <> '-'  label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0),2)))}, 
 "select Col1,Col2,Col7,Col4,Col5,Col6 order by Col7", 0), 
 "format Col3 'dd-mmm-yy'", 0), "30-Nov-20", ""))
Solution

  • this should work:

    =ARRAYFORMULA(SUBSTITUTE(QUERY(QUERY({QUERY({
 IFERROR(QUERY(    General!A2:P, "select 'General',K,G,E,H,I,J   where not E contains '-'  label 'General'''",   0), {"","","","","","",""});
 IFERROR(QUERY(      Salud!A2:P, "select 'Salud',K,G,E,H,I,J     where not E contains '-'  label 'Salud'''",     0), {"","","","","","",""});
 IFERROR(QUERY(    Parejas!A2:P, "select 'Parejas',K,G,E,H,I,J   where not E contains '-'  label 'Parejas'''",   0), {"","","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,E,H,I,J where not E contains '-'  label 'Paz Hogar'''", 0), {"","","","","","",""});
 IFERROR(QUERY(   Sustento!A2:P, "select 'Sustento',K,G,E,H,I,J  where not E contains '-'  label 'Sustento'''",  0), {"","","","","","",""});
 IFERROR(QUERY(      Hijos!A2:P, "select 'Hijos',K,G,E,H,I,J     where not E contains '-'  label 'Hijos'''",     0), {"","","","","","",""});
 IFERROR(QUERY(  Educacion!A2:P, "select 'Educacion',K,G,E,H,I,J where not E contains '-'  label 'Educacion'''", 0), {"","","","","","",""});
 IFERROR(QUERY(      Otros!A2:P, "select 'Otros',K,G,E,H,I,J     where not E contains '-'  label 'Otros'''",     0), {"","","","","","",""});
 IFERROR(QUERY(         NA!A2:P, "select 'NA',K,G,E,H,I,J        where not E contains '-'  label 'NA'''",        0), {"","","","","","",""});
 IFERROR(QUERY(          a!O2:P, "select 'a',K,G,E,H,I,J         where not E contains '-'  label 'a'''",         0), {"","","","","","",""})},
 "where Col1 is not null and not Col1 contains 'NA'", 0), DATE(20&RIGHT(QUERY({
 IFERROR(QUERY(    General!A2:P, "select 'General',K,G,E,H,I,J   where not E contains '-'  label 'General'''",   0), {"","","","","","",""});
 IFERROR(QUERY(      Salud!A2:P, "select 'Salud',K,G,E,H,I,J     where not E contains '-'  label 'Salud'''",     0), {"","","","","","",""});
 IFERROR(QUERY(    Parejas!A2:P, "select 'Parejas',K,G,E,H,I,J   where not E contains '-'  label 'Parejas'''",   0), {"","","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,E,H,I,J where not E contains '-'  label 'Paz Hogar'''", 0), {"","","","","","",""});
 IFERROR(QUERY(   Sustento!A2:P, "select 'Sustento',K,G,E,H,I,J  where not E contains '-'  label 'Sustento'''",  0), {"","","","","","",""});
 IFERROR(QUERY(      Hijos!A2:P, "select 'Hijos',K,G,E,H,I,J     where not E contains '-'  label 'Hijos'''",     0), {"","","","","","",""});
 IFERROR(QUERY(  Educacion!A2:P, "select 'Educacion',K,G,E,H,I,J where not E contains '-'  label 'Educacion'''", 0), {"","","","","","",""});
 IFERROR(QUERY(      Otros!A2:P, "select 'Otros',K,G,E,H,I,J     where not E contains '-'  label 'Otros'''",     0), {"","","","","","",""});
 IFERROR(QUERY(         NA!A2:P, "select 'NA',K,G,E,H,I,J        where not E contains '-'  label 'NA'''",        0), {"","","","","","",""});
 IFERROR(QUERY(          a!O2:P, "select 'a',K,G,E,H,I,J         where not E contains '-'  label 'a'''",         0), {"","","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 2), MONTH(MID(QUERY({
 IFERROR(QUERY(    General!A2:P, "select 'General',K,G,E,H,I,J   where not E contains '-'  label 'General'''",   0), {"","","","","","",""});
 IFERROR(QUERY(      Salud!A2:P, "select 'Salud',K,G,E,H,I,J     where not E contains '-'  label 'Salud'''",     0), {"","","","","","",""});
 IFERROR(QUERY(    Parejas!A2:P, "select 'Parejas',K,G,E,H,I,J   where not E contains '-'  label 'Parejas'''",   0), {"","","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,E,H,I,J where not E contains '-'  label 'Paz Hogar'''", 0), {"","","","","","",""});
 IFERROR(QUERY(   Sustento!A2:P, "select 'Sustento',K,G,E,H,I,J  where not E contains '-'  label 'Sustento'''",  0), {"","","","","","",""});
 IFERROR(QUERY(      Hijos!A2:P, "select 'Hijos',K,G,E,H,I,J     where not E contains '-'  label 'Hijos'''",     0), {"","","","","","",""});
 IFERROR(QUERY(  Educacion!A2:P, "select 'Educacion',K,G,E,H,I,J where not E contains '-'  label 'Educacion'''", 0), {"","","","","","",""});
 IFERROR(QUERY(      Otros!A2:P, "select 'Otros',K,G,E,H,I,J     where not E contains '-'  label 'Otros'''",     0), {"","","","","","",""});
 IFERROR(QUERY(         NA!A2:P, "select 'NA',K,G,E,H,I,J        where not E contains '-'  label 'NA'''",        0), {"","","","","","",""});
 IFERROR(QUERY(          a!O2:P, "select 'a',K,G,E,H,I,J         where not E contains '-'  label 'a'''",         0), {"","","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 4, 3)&1), LEFT(QUERY({
 IFERROR(QUERY(    General!A2:P, "select 'General',K,G,E,H,I,J   where not E contains '-'  label 'General'''",   0), {"","","","","","",""});
 IFERROR(QUERY(      Salud!A2:P, "select 'Salud',K,G,E,H,I,J     where not E contains '-'  label 'Salud'''",     0), {"","","","","","",""});
 IFERROR(QUERY(    Parejas!A2:P, "select 'Parejas',K,G,E,H,I,J   where not E contains '-'  label 'Parejas'''",   0), {"","","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,E,H,I,J where not E contains '-'  label 'Paz Hogar'''", 0), {"","","","","","",""});
 IFERROR(QUERY(   Sustento!A2:P, "select 'Sustento',K,G,E,H,I,J  where not E contains '-'  label 'Sustento'''",  0), {"","","","","","",""});
 IFERROR(QUERY(      Hijos!A2:P, "select 'Hijos',K,G,E,H,I,J     where not E contains '-'  label 'Hijos'''",     0), {"","","","","","",""});
 IFERROR(QUERY(  Educacion!A2:P, "select 'Educacion',K,G,E,H,I,J where not E contains '-'  label 'Educacion'''", 0), {"","","","","","",""});
 IFERROR(QUERY(      Otros!A2:P, "select 'Otros',K,G,E,H,I,J     where not E contains '-'  label 'Otros'''",     0), {"","","","","","",""});
 IFERROR(QUERY(         NA!A2:P, "select 'NA',K,G,E,H,I,J        where not E contains '-'  label 'NA'''",        0), {"","","","","","",""});
 IFERROR(QUERY(          a!O2:P, "select 'a',K,G,E,H,I,J         where not E contains '-'  label 'a'''",         0), {"","","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 2))}, 
 "select Col1,Col2,Col8,Col4,Col5,Col6 order by Col8", 0), 
 "format Col3 'dd-mmm-yy'", 0), "30-Nov-20", ""))