Dynamic Columns - Excel Pivot Tables

[Wayne Dunlop]

Hi,
I read Fernand's question aout Dynamic columns and decided it was time to
write, once again, to the list about the power of coupling Excel Pivot
tables to PH Quiz.
Let Quiz churn out a CSV-style list of the data, then FTP the CSV file over
to a PC with Excel and let Excel take care of getting the data into a table.

In French, "Pivot Tables" are called "Dynamic CrossTables".
The "pivot" notion refers to the fact that you can rearrange the data
display easily using the mouse but doesn't get across the "dynamic" idea,
which is a pity.
Pivot tables are "dynamic" in three senses :
1. They can re-read, or "update themselves" from a remote data source a
regular intervals
2. They will add (or subtract) rows and columns according to the data they
find in the source file.
3. They do all sorts of calculations on the data found in the file.

Cognos had its own product that did this. I'm sorry I can't remember the
name of it, but we had Excel bundled on our PCs and it handles about 99% of
our needs.

Here is an example of how to get Quiz to prepre the data.
There are howtos fot Pivot Tables out there.

!JOB LOFTY,MGR.ACCOUNT etc

!QUIZ

acc *lloft2 link viaindex Clientnum to Client

def oper char *2 = Clientori[1:2]

def fich char *2 = Clientori[3:2]

def mess char *2 = Clientori[5:2]





def header char  * 100  =    &,

"ClientID;DateCreation;Origin;Mailing;Payments;Amounts;Oper;Fich;Mess;X"



:purge lloft3

set rep dev disc name lloft3

def packet char * 100 =                        &,

pack (       asc(ClientID)           +  ";" +  &,

             asc(Clientdate,8)[7:2]  +  "/" +  &,

             asc(Clientdate,8)[5:2]  +  "/" +  &,

             asc(Clientdate,8)[1:4]  +  ";" +  &,

             "'" +     Clientori     +  ";" +  &,

             asc(mailctr)            +  ";" +  &,

             asc(venctr)             +  ";" +  &,

             asc(Amount2,13)[ 1:11]  +  "." +  &,

             asc(Amount2,13)[12:2 ]  +  ";" +  &,

             oper                    +  ";" +  &,

             fich                    +  ";" +  &,

             mess                    +  ";" +  &,

             "X"  )



set nohead

set rep spa 0

set page len 0 wid 200

init head header

rep packet

go



:ftp

open 192.168.0.33

user username

pass password

put lloft3 lloft3.csv

quit

e

!EOJ



:print lloft3

ClientID;DateCreation;Origin;Mailing;Payments;Amounts;Oper;Fich;Mess;X

1;08/11/1989;'AKAXAI;1;0;00000000000.00;AK;AX;AI;X

45;03/09/1984;'000100;1;0;00000000000.00;00;01;00;X

47;31/08/1984;'000100;1;0;00000000000.00;00;01;00;X

58;31/08/1984;'000100;1;0;00000000000.00;00;01;00;X

73;10/01/1986;'000000;1;1;00000000100.00;00;00;00;X

76;03/09/1984;'ATCDEK;1;1;00000000100.00;AT;CD;EK;X

82;19/08/1984;'000100;1;0;00000000000.00;00;01;00;X

86;03/09/1984;'000100;1;0;00000000000.00;00;01;00;X



The basic idea is to get the CSV data into an Excel Pivot Table.

Pivot Tables can handle up to 256 columns. Dynamically.

The table will adapt the number of columns according to the data in the CSV
file.

If the number of columns is greater than 256 you can group the columns on
criteria.

For example, Date columns can be grouped by Year, Semester, Quarter, Month,
Week etc.



(And the new version (Office v.12, still in beta) can handle an unlimited
number of columns!)



Notes:

--------------

My example shows

a number – Client ID

a Date  - DateCreation

a field where the data may be alpha or numerical so that's why the
apostrophe is added on to the left of the data. – Origin,

because Excel will trim off any leading zeroes in display. The Apostrophe
keeps them in, not very elegant, but it works.

A money field with two figures after the decimal point. – Payments.

And the Origin field split up into its three components. Oper, Fich, Mess.

The X field is just a way of getting rid of any trailing spaces.



And if the Pivot table seems a tricky weird way of doing things, well it is
!
I agree up to a point. If you have never tried it.

Once you have the hang of it, it is mind-blowingly powerful. Well worth the
effort.
If you have mastered QUIZ the you can master Pivot Tables.



The nec plus ultra is to open the CSV file as External data and to save your
Excel file as an Empty Template. "Empty" meaning without the data.

File, Save As, Template, Empty? Reply "Yes" and Excel will reload the data
when you reopen the template. So you get an auto update every tim the file
is opened.
(Couple this to the fact that you can get the CSV file generated in a JOB
that runs itself at regular intevals...  so that the data file is
regenerated every so often and the JOB can handle the FTP transfer too. )

If the data has changed in the meantime, Excel will automatically
recalculate summaries, averages etc. and add in or take out columns and
lines depending on the new data.

Another thing is that the tables can be printed only when needed. Authorized
users can access the Excel files across the network.



HTH



Wayne Dunlop
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20060921/42873ed8/attachment.html