I have a table that has GL ACCOUNT BALANCES. It has numbers for each GL account for each year. The record has closing balance for each period, although PERIOD is not a field on the table. I really need to have PERIOD as a field to link to the transactions table.
I know there is a way to loop through the table and pull out a portion of the field to use as the period.
field name: GL_CLOSING_BALANCE_01
The period is 01
same for GL_CLOSING_BALANCE_02 through 12
I would like to loop through for i=01-12 and have a closing balance for each period. I would like for the user to be able to choose a PERIOD and have the report pull the correct closing balance for them.
I am pulling this info from a qvd called GL_ACCOUNT_BALANCES.QVD
I'm not sure if I have given enough information.
You don't need a loop for this - you could just use The Crosstable Load to transform your data into a "normal" table-structure and then you could use some string-functions to get the period-informations from the field-value, for example: keepchar(FIELD, 0123456789).
Thanks. I got the crosstable to work but still don't have the PERIOD.
LOAD [GL Account],
[Year to Date DR],
[Year to Date CR],
[Period 01 DR],
[Period 01 CR],
[Period 02 DR],
[Period 02 CR], ........
There is no period in the table. I need to have period=01 for period 01 DR and Period 01 CR, period=02 for period 02 DR and CR...... I don't know how to have it create a numeric field from another field name.
Any help would be appreciated.
I would use something like this in an on the crosstable following load:
Load * from SOURCE;
makedate([Current Year], num(keepchar(PERIOD, '0123456789'))) as Date,
right(PERIOD,2) as Type,
drop tables crosstables;
I really appreciate your time on this. I'm trying to understand your code.
Here is what I have. I have played around with the fields a little to make sure they are named correctly.
The whole top section is the crosstable that you coded.
If I stop after drop crosstable, it runs and I get some data. But then I try to add the fields I need based on this new field called period, and it fails but I don't get an error. It just says the data didn't load.
Any suggestions to move on from here.
Now that I have period, I need to use it. I need to show the opening balance for period 01-12. That is what I'm trying to do with the new piece of code I acted.
ACCOUNT_NUMBER AS GL_ACCT_KEY,
makedate(CURRENT_YEAR, num(keepchar(PERIOD, '0123456789'))) as Date,
right(PERIOD,2) as PERIOD,
drop table crosstable;
DROP TABLE FINAL;
I don't think that I would use such nested-if loop - I would extract all informations from the fiel PERIOD with string-functions - two are mentioned above to create a date (which will be later connected to a master-calendar) and a Type field related to DR respectively CR. For example:
subfield(PERIOD, '_', 2)
to get a certain substring from my field or maybe more suitable here something like:
pick(wildmatch(PERIOD, '*CLOSE*', '*OENING*', '*DR*', '*CR*'),
'Close', 'Open', 'DR', 'CR')
maybe extended to further values and/or splitted into several Type fields. Also I would think to exclude some not needed values - for example if there are any TOTAL's within them because they would be calculated from the other values.
Another point which could make the life much easier would be if you could load the source-data to this huge crosstable-report. Quite probably there is a nice data-structure within a database and with much efforts will be such a report created and than if you need to work with the data from it you need much efforts to re-create a workable data-structure - it's not uncommon but it made not much sense ...
The 4th subfield, i.e.
Subfield(myField, '_',1) = GL
Subfield(myField, '_',2) = CLOSING
Subfield(myField, '_',3) = BALANCE
Subfield(myField, '_',4) = 01
Note thaat you may want to do sometign like:
'Period' & Subfield(myField, '_',4) AS Period
Which would yield 'Period01' for he field value.
Thanks. That makes perfect sense.
What I want is for the user to be able to choose a period (1-12) and see the closing balance for that period.
I used your example and created this:
Subfield(GL_CLOSE_BALANCE_01,'_',4) AS PERIOD
This runs ok, but period is blank when I look at the data. I would have thought I would have seen an 01 for the period data. So this makes a column called PERIOD, but it has no data.
I still don't think this gets me there even if I get it working. I feel I need to say if the field name is gl close balance 01 then period =01, if field name is gl close balance 02, then period = 02. Or some type of loop through to get all of the periods.
There are a few fields that I will need to link to that period once I get it.
GL CLOSE BALANCE 01 THROUGH 12
GL_BALANCE_01 THROUGH 12