Calc and datapilot

users
#1

First of all I'm new to LibreOffice and i'm kind of a numbskull if it comes
to the more advanced options within this software.

What I'm trying to achieve is the following, I've got data consisting of
"code" "date" "text" "value" which I keep journalizing. I want to manipulate
this data in a different sheet in a manner that I want the whole rows parsed
to the different sheet depending on which "code" and "date" I'm looking for.

See the following picture:

[IMG]http://i54.tinypic.com/20ubpdt.jpg[/IMG]

I've been toying aroudn with DataPilot now for quite some time and I can
sort nicely on "code" but I fail to parse entire rows like I want to.

Anybody has a clue how to solve this? It seems like an easy problem but to
keep the journal as it is it gets a bit complicated. (solutions like Sort
don't work out in such case I think).

#2

Hence I'm looking into datapilot maybe somebody has a way easier solution to
get done what I feel like. It sounds like an easy problem to have 4 fields
in a row and to sort specifically elsewhere the data without tempering with
the original journal yet it kept me sofar quite busy. Meddling with
datapilot as well sort didn't work out and I'm to much of an apprentice to
start poking around in Base (though I do manage to get the DB filled I fail
to extract it in a proper manner in Calc.)

Anyways hopefully knows a solution for the problem.

#3

Hi

Hence I'm looking into datapilot maybe somebody has a way easier solution to
get done what I feel like. It sounds like an easy problem to have 4 fields
in a row and to sort specifically elsewhere the data without tempering with
the original journal yet it kept me sofar quite busy. Meddling with
datapilot as well sort didn't work out and I'm to much of an apprentice to
start poking around in Base (though I do manage to get the DB filled I fail
to extract it in a proper manner in Calc.)

Anyways hopefully knows a solution for the problem.

--
View this message in context: http://nabble.documentfoundation.org/Calc-and-datapilot-tp2856239p2863063.html
Sent from the Users mailing list archive at Nabble.com.

What type of plot do you want to do? Calc supports many different plot
types. Generally you would put the x values in a column and each y value
in a separate columns, and use the wizard.

#4

Hi Jay,

Thanks for the reply though I made a mistake in the topic. I'm trying to get
this done with Spreadsheet and not Calc.

#5

Hi,

JZ1982 wrote (23-04-11 23:43)

First of all I'm new to LibreOffice and i'm kind of a numbskull if it comes
to the more advanced options within this software.

What I'm trying to achieve is the following, I've got data consisting of
"code" "date" "text" "value" which I keep journalizing. I want to manipulate
this data in a different sheet in a manner that I want the whole rows parsed
to the different sheet depending on which "code" and "date" I'm looking for.

See the following picture:

[IMG]http://i54.tinypic.com/20ubpdt.jpg[/IMG]

I've been toying aroudn with DataPilot now for quite some time and I can
sort nicely on "code" but I fail to parse entire rows like I want to.

Getting the result as on the right side of your picture, can easily be done with the default filter.
   Data > Filter > Default
Then filter on code 402 and copy/paste the result to a different place, when needed.

Hmm, but is it this what you want to achieve?

Kind regards,
Cor

#6

Hi Cor,

Though with a copy paste I could get it easily done I would like to get it updated automatically. As my journal keeps growing it would require me to regulary filter and copy/paste. Also I would like todo it on other codes.

Kind regards,

JZ

#7

Hi,

I don't think you can use Calc in the way you invision.

Some options exist...

1. Create a macro that sorts the list. This is not dynamic.
2. Create a duplicate of the sheet containing the core data. Use VLOOKUP to dynamically duplicate the sheet. Then create a filter on each duplicate sheet. Each duplicate sheet is filtered based on a particular 'volgnummer'. Usually I put this value at the top of the file as a header. This works really well with small to medium datsets but can be slow with larger datasets.
3. For larger datasets consider storing in a database then use Base to import just the 'code' lines you want to each sheet. This is a bit more complicated and less portable, depending on the database used.

#8

________________________________
From: Simon Cropper <scropper@botanicusaustralia.com.au>
To: users@libreoffice.org
Sent: Wed, 27 April, 2011 11:00:22
Subject: Re: [libreoffice-users] Calc and datapilot

On 27/04/11 18:18, Cor Nouws wrote:

Hi,

JZ1982 wrote (23-04-11 23:43)

First of all I'm new to LibreOffice and i'm kind of a numbskull if it
comes
to the more advanced options within this software.

What I'm trying to achieve is the following, I've got data consisting of
"code" "date" "text" "value" which I keep journalizing. I want to
manipulate
this data in a different sheet in a manner that I want the whole rows
parsed
to the different sheet depending on which "code" and "date" I'm
looking for.

See the following picture:

[IMG]http://i54.tinypic.com/20ubpdt.jpg[/IMG]

I've been toying aroudn with DataPilot now for quite some time and I can
sort nicely on "code" but I fail to parse entire rows like I want to.

Getting the result as on the right side of your picture, can easily be
done with the default filter.
Data > Filter > Default
Then filter on code 402 and copy/paste the result to a different place,
when needed.

Hmm, but is it this what you want to achieve?

Kind regards,
Cor

Hi,

I don't think you can use Calc in the way you invision.

Some options exist...

1. Create a macro that sorts the list. This is not dynamic.
2. Create a duplicate of the sheet containing the core data. Use VLOOKUP to
dynamically duplicate the sheet. Then create a filter on each duplicate sheet.
Each duplicate sheet is filtered based on a particular 'volgnummer'. Usually I
put this value at the top of the file as a header. This works really well with
small to medium datsets but can be slow with larger datasets.
3. For larger datasets consider storing in a database then use Base to import
just the 'code' lines you want to each sheet. This is a bit more complicated and
less portable, depending on the database used.

Cheers Simon

Hi,
I recommend using Base for this.  You are really trying to use Calc as a
database; square-peg, round-hole.  Proper database programs pull off this type
of report really easily and it's even difficult to stop it producing things like
this.  With Calc (or any other spreadsheet program) you will get into all sorts
complicated expressions that may or may not be compatible with other programs. 
Take the easier route and use Base even if the table is quite small at this
stage.

Base seems to prefer to have external tables to work from and can read the Calc
spreadsheet as a table so all the programs will be happy to work with what you
have already.  Access (the MS database program) can also use external tables but
Base is likely to be better.

Sadly we don't have many database experts in LibreOffice at the moment (unless
thousands joined while in was on the boat) but the OpenOffice forums seem happy
to deal with any questions.  Just say Base 3.3.2 or whatever rather than
creating an issue about whether it is OOo or LO.  The version numbers give a
good clue to the right people without their bosses being any the wiser.

Good luck and regards from
Tom :slight_smile:

#9

I'm more and more convinced that for a dynamic solution Base is the answer so
today I started fiddling a bit.

The journal I saved, started Base > connect to an existing database >
spreadsheet > select file > finish

Then select on the left below Database Queries, create Query in Design View
where for now I selected all columns I want. I got to see then basically the
whole journal I saved and ended the program.

Started Calc, saved a blanc file. Tools > LibreOffice Base > New, select the
just saved DB where it will show up then as a Registered database.

Press F4 where you get to see the present databases where the selected
database also is visible (created Query in Base as well the Table it self).
Next will be how to make a query that basically consists of making a
selection based upton "code" and "boekdatum".

Slowly progress!

#10

HI :slight_smile:
Progress is good even if it is slow. Hopefully it might get faster as you
become more familiar with the program. I must admit i haven't played around
with it myself much.
Good luck and many regards from
Tom :slight_smile: