Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date
Hi Hylton On 10/08/2022 19:28, Hylton Conacher (ZR1HPC) wrote: Hi Steve, et al, On 2022/08/09 23:05, Steve Edmonds wrote: ... Further to Johnny's question, how is the CSV delimited. Do you just have commas between values or are some values also quoted in " ". There are no " "'s, all comma separated. When I import a simple CSV using Sheet>Insert sheet from file I can set the simple date column as type date at import and using the MAXIFS solution proposed by Michael [ in my test =MAXIFS($C$2:$C$18,$B$2:$B$18,">=",$B2:$B$18,"<"&(G2+1)) ] works well in a test. Steve, As a courtesy I have sent you a month's worth of data in a csv file downloaded from my Ambient Weather station cloud account. Try it, as by no means is it a simple one especially when all you want is the rainfall on each of the days of the month. into Base My understanding is that VLOOKUP only returns 1 value, rather than a vector from which to determine a date. I believe what I need to accomplish cannot be done with VLOOKUP and that a combination of INDEX(MATCH()) is a better solution especially given that the RAW Data file import is going to grow approximately 1MB per month, despite being just .csv type. Moving on from this means that the whole file should preferably be manipulated in Base. Great idea. I currently do a similar thing loading the data csv file direct into MySQL every day with a Perl script and then display my queries and analysis in web pages for our employees using PHP. For me this is much simpler and more flexible and than Calc or Base, especially in a multi-user environment Sorry for wasting everyone's time, Base questions coming soon 0:) Hylton -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] [CLOSED] VLOOKUP Syntax to find MAX on a particular date
Dear fellow members, My apologies for wasting your time and brain power, however the use of Calc and VLOOKUP is NOT the right tool for the job and that INDEX(MATCH) would have ben better, given that the downloaded .csv data file grows by approximately 1MB per month. I have marked this thread as closed and shall be starting a new thread as I move the data file to Base. Expect the Base questions soon 0:) Apologies again Thank-you Hylton On 2022/08/08 21:25, Hylton Conacher (ZR1HPC) wrote: Hi, Using LO Calc 7.3.2.2 and I have a 28 column table RAW Data table in excess of 65k rows. A three column sample below: Date Simple Date Outdoor Temp 2022-01-01T00:05:00+02:00 2022-01-01 00:05:00 17.5 2022-01-01T00:00:00+02:00 2022-01-01 00:00:00 17.4 I have a secondary table, called Amended RAW Data containing just the 10 digit date and the corresponding value of the column value I am seeking, whether it be the Outdoor Temp, Daily Rainfall etc What I need to do is query the following table, searching for a specific 10 digit date, and find the max value on a specific date. I have tried so many different itinerations on the VLOOKUP formula and browsed many elementary Google VLOOKUP pages that I need help. I have tried(all return N/A): =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62037,10),$'RAW Data'.$B$2:$L$62037,1,FALSE)) =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11)) =MAX(VLOOKUP($A11,$'RAW Data'.$B$2:$M$62037,11)) =MAX(VLOOKUP(LEFT($'RAW Data'.$B$2:$B$62307,10)=$'Amended RAW Data'.$A$11,$'RAW Data'.$B$2:$L$62037,11,0)) References to 'Amended RAW Data'.$A$11 refer to a date that I know has greater than zero value, and the 11th column data I want to max, within a date. Help appreciated Hylton -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculated values in View or Query
Hi Harvey, The Base Guide answers my question precisely, listing as it does the built-in functions together with a remark in each case "[Works in the GUI]" - or not (e.g. CASE) Great stuff!! And all this translated from German Base Handbuch into English. I take this as special compliment. Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculated values in View or Query
That is marvellous. Thank you, Robert!! I must get used to visiting the Document Foundation website, as I tend to stick with the help function and otherwise OpenSUSE. Excellent tip. The Base Guide answers my question precisely, listing as it does the built-in functions together with a remark in each case "[Works in the GUI]" - or not (e.g. CASE) Great stuff!! Harvey On Wed, 2022-08-10 at 10:06 +0200, Robert Großkopf wrote: > Hi Harvey, > > most SQL code I create direcly without using GUI, so I don't know if > all > of this could also be created through GUI. > > Have a look at the Base Guide > https://nextcloud.documentfoundation.org/s/qjFkGwpEEkNrt6f. > The GUI has been created to work perfect with internal HSQLDB (very > old…), but there is no special SQL-version, which isn't running in LO > Base. Base Guide will show you the code for internal HSQLDB and also > for > Firebird - and some hints for other databases. > > Regards > > Robert > -- > Homepage: https://www.familiegrosskopf.de/robert > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculated values in View or Query
Hi Harvey, most SQL code I create direcly without using GUI, so I don't know if all of this could also be created through GUI. Have a look at the Base Guide https://nextcloud.documentfoundation.org/s/qjFkGwpEEkNrt6f. The GUI has been created to work perfect with internal HSQLDB (very old…), but there is no special SQL-version, which isn't running in LO Base. Base Guide will show you the code for internal HSQLDB and also for Firebird - and some hints for other databases. Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculated values in View or Query
Hi Robert, I suppose my question could be reformulated: what version of SQL does Base support? To what extent is that SQL supported in the BASE query/view graphical interface? I haven't been able to find an answer in the LO Help (yet). Cheers Harvey On Wed, 2022-08-10 at 08:31 +0200, Robert Großkopf wrote: > Hi Harvey, > > > > For example, I would like to extract a text string from a field in > > each > > record and display it in an extra view and/or query field, or > > multiply > > two field values and record the result in an extra field. > > A little example will help: What is the content of the field in tehe > table, whot should be shown in the query? > > https://ask.libreoffice.org will be a better place for this, because > you > could post screenshots and add a simple example database. > > Regards > > Robert > -- > Homepage: https://www.familiegrosskopf.de/robert > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] VLOOKUP Syntax to find MAX on a particular date
Hi Steve, et al, On 2022/08/09 23:05, Steve Edmonds wrote: ... Further to Johnny's question, how is the CSV delimited. Do you just have commas between values or are some values also quoted in " ". There are no " "'s, all comma separated. When I import a simple CSV using Sheet>Insert sheet from file I can set the simple date column as type date at import and using the MAXIFS solution proposed by Michael [ in my test =MAXIFS($C$2:$C$18,$B$2:$B$18,">=",$B2:$B$18,"<"&(G2+1)) ] works well in a test. Steve, As a courtesy I have sent you a month's worth of data in a csv file downloaded from my Ambient Weather station cloud account. Try it, as by no means is it a simple one especially when all you want is the rainfall on each of the days of the month. into Base My understanding is that VLOOKUP only returns 1 value, rather than a vector from which to determine a date. I believe what I need to accomplish cannot be done with VLOOKUP and that a combination of INDEX(MATCH()) is a better solution especially given that the RAW Data file import is going to grow approximately 1MB per month, despite being just .csv type. Moving on from this means that the whole file should preferably be manipulated in Base. Sorry for wasting everyone's time, Base questions coming soon 0:) Hylton -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Calculated values in View or Query
Hi Harvey, For example, I would like to extract a text string from a field in each record and display it in an extra view and/or query field, or multiply two field values and record the result in an extra field. A little example will help: What is the content of the field in tehe table, whot should be shown in the query? https://ask.libreoffice.org will be a better place for this, because you could post screenshots and add a simple example database. Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
