[Solved] Repeat latest group 1 for each group 2

Discuss the database features

[Solved] Repeat latest group 1 for each group 2

Postby DynV » Sun Oct 18, 2020 11:51 am

I have 2 type of data, which are labeled group 1 & 2, which come in order and the 2nd type most likely is repeated; for example
1, A
2, B
2, C
2, D
1, E
2, F
2, G
1, H
2, I
2, J
2, K

I'd like for all the group 2 entries to mention its latest group 1; working with the previous example ending up with
A, B
A, C
A, D
E, F
E, G
H, I
H, J
H, K

I'm not sure which tool from the OO suite would help me with such issue, but I suspect OO Base would.

Thank you kindly for your help.
Last edited by robleyd on Tue Oct 20, 2020 1:38 am, edited 4 times in total.
Reason: Add green tick
Je suis francophone.
User avatar
DynV
 
Posts: 193
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: Repeat latest group 1 for each group 2

Postby MrProgrammer » Sun Oct 18, 2020 9:52 pm

DynV wrote:I have 2 type of data, which are labeled group 1 & 2, which come in order and the 2nd type most likely is repeated … I'd like for all the group 2 entries to mention its latest group 1; working with the previous example
This is easily accomplished in Calc with an IF() function. Click the dropdown in Cell A1 and select group 2.
202011181508.ods
(12.49 KiB) Downloaded 6 times
 Edit: Replaced earlier attachment with this simpler one. 

If your source data, shown as two values separated by commas, is actually in a single cell, first split it in two using [Tutorial] Text to Columns. Since the format of your source data is not clear to me, please attach a document demonstrating the situation next time (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). You will find that you receive better answers when you attach your data.

[Tutorial] Ten concepts that every Calc user should know.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3946
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Repeat latest group 1 for each group 2

Postby DynV » Sun Oct 18, 2020 11:11 pm

The example was included in a Base forum, not Calc; I understand the latter would be prone to confusion, but the former seem to me pretty clear the quoted content included CSV, and yes it's CSV.

For archival or those that can't open the file, the 1st row is some odd header system, and the 1st 2 col(umns) are as in the OP example, and 3rd col contains
Code: Select all   Expand viewCollapse view
=IF(A2=1,B2,C1)
which I don't really understand why the 1st content row would fetch something in what seems to be the header, but anyway it will never happen as that condition for the 1st content will never happen... well with right data, but this isn't to be a commercial process to check for everything. :) I marked the thread as solved as although I don't fully understand the issue at the beginning of this paragraph, the important part to just copy the 1st content group 1 over then check if the row is a group 1, and if not just copy the cell above.

I don't know why I didn't think of that. I was convinced this was something often seen in commercial situations (ie going through logs), and there would be tools for it; perhaps a set of macros or templates.

Thank you again
Je suis francophone.
User avatar
DynV
 
Posts: 193
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: Repeat latest group 1 for each group 2

Postby DynV » Sun Oct 18, 2020 11:25 pm

I removed the solved tag as I realized there's something not addressed, that the group 1 rows not be included; if what I'm referring to is unclear, please refer to the OP 2nd quote, a reminder both are CSV.
Je suis francophone.
User avatar
DynV
 
Posts: 193
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: Repeat latest group 1 for each group 2

Postby Villeroy » Sun Oct 18, 2020 11:45 pm

I'm not sure which tool from the OO suite would help me with such issue, but I suspect OO Base would.

This is easy to do in Calc as demonstrated by MrProgrammer. If it needs to be done in Base, there has to be a column which determines the order of appearance, a time stamp or auto-ID.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28647
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Repeat latest group 1 for each group 2

Postby Villeroy » Mon Oct 19, 2020 7:53 am

May be something like this. I'm not sure if this is a valid solution.
Attachments
t1033366.odb
(4.19 KiB) Downloaded 5 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28647
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Repeat latest group 1 for each group 2

Postby DynV » Tue Oct 20, 2020 1:32 am

The issue is solved for this particular case but it would be nice to have it a bit "better" so I can use the method in a future similar situation.

MrProgrammer wrote:Click the dropdown in Cell A1 and select group 2.

I saw it was selected and that something was displayed (as opposed to some error code instead) so didn't think it caused a problem to remain that way. I didn't realize you meant to unselect all but it.

Perhaps you'd like to explain how the system of how unselecting make it display as I desire (the thing that make the header I assume).

Villeroy wrote:May be something like this. I'm not sure if this is a valid solution.

Thank you again. Is there a way to automate filling the table? Either reading from the file containing the data (flat-file DB), or something with an automated import process (ie pasting in Calc).
Je suis francophone.
User avatar
DynV
 
Posts: 193
Joined: Tue Apr 06, 2010 10:50 pm
Location: Montreal, Canada

Re: [Solved] Repeat latest group 1 for each group 2

Postby Villeroy » Tue Oct 20, 2020 12:50 pm

1) you can connect a Base document to the flat-file DB and copy a table icon onto a table icon in the other DB (mine is an embedded HSQLDB).
2) you can connect a text file to the embedded HSQLDB so it appears as a linked text table and copy that icon onto the binary table. [Tutorial] Using csv/text files as editable data source.
3) you can open the file with Calc, copy the range and paste onto the table.

You can export any table, view or query into spreadsheets: [Tutorial] Using registered datasources in Calc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28647
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Base

Who is online

Users browsing this forum: epuchalski and 6 guests