need to loop through to get period from field

[#1]

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.

Example:

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.

Any advice?

I am pulling this info from a qvd called GL_ACCOUNT_BALANCES.QVD

I'm not sure if I have given enough information.

[marcus_sommer]

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).

- Marcus

[#3]

Thanks.  I got the crosstable to work but still don't have the PERIOD.

CrossTable([PERIOD], Data,3)

LOAD [GL Account],

     [Current Year],

     [Balance Type],

     Currency,

     [Status Flag],

     [Year to Date DR],

     [Year to Date CR],

     [Opening DR],

     [Opening 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.

[marcus_sommer]

I would use something like this in an on the crosstable following load:

crosstable:

crosstable([PERIOD], Data,5)

Load * from SOURCE;

final:

load

     [GL Account],

     [Balance Type],

     Currency,

     [Status Flag],

     makedate([Current Year], num(keepchar(PERIOD, '0123456789'))) as Date,

     right(PERIOD,2) as Type,

     Data

resident crosstable;

drop tables crosstables;

- Marcus

[#5]

Marcus,

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.

crosstable:

crosstable([PERIOD], Data,5)

Load *

FROM

[$(vQVD)FIN_GL_ACCOUNT_BALANCES.QVD]

(qvd);

final:

load

     ACCOUNT_NUMBER AS GL_ACCT_KEY,

     ACCOUNT_IDENT,

     SYS_CURRENCY_CODE,

     //DELETE_FLAG,

     makedate(CURRENT_YEAR, num(keepchar(PERIOD, '0123456789'))) as Date,

     right(PERIOD,2) as PERIOD,

     Data

resident crosstable;

drop table crosstable;

TEMP_GL:

LOAD *,

//OPENING BALANCE

IF(PERIOD='01',(GL_BALANCE_00),

  IF(PERIOD='02',(GL_CLOSE_BALANCE_01),

  IF(PERIOD='03',(GL_CLOSE_BALANCE_02),

  IF(PERIOD='04',(GL_CLOSE_BALANCE_03),

  IF(PERIOD='05',(GL_CLOSE_BALANCE_04),

  IF(PERIOD='06',(GL_CLOSE_BALANCE_05),

  IF(PERIOD='07',(GL_CLOSE_BALANCE_06),

  IF(PERIOD='08',(GL_CLOSE_BALANCE_07),

  IF(PERIOD='09',(GL_CLOSE_BALANCE_08),

  IF(PERIOD='10',(GL_CLOSE_BALANCE_09),

  IF(PERIOD='11',(GL_CLOSE_BALANCE_10),

  IF(PERIOD='012',(GL_CLOSE_BALANCE_11),))))))))))))AS GL_PERIOD_OPEN_BAL,

RESIDENT FINAL;

DROP TABLE FINAL;

[marcus_sommer]

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 ...

- Marcus

[Anonymous]

In a Load script you could also do:

Load

, ...

, Subfield(myField, '_',4) AS Period

;

Where myField has values like GL_CLOSING_BALANCE_01

[#8]

That sounds like a plan.  What does the 4 mean?

[Anonymous]

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:

Load

'Period' & Subfield(myField, '_',4) AS Period

Which would yield 'Period01' for he field value.

[#10]

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

ETC.