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.

[Anonymous]

I was presuming that your data looked something like:

dataType                                             data

GL_BALANCE_01 THROUGH 12          45.6

GL_CLOSE_BALANCE_01                    37.4

In which case subfield(dataType, '_',4) AS Period would populate the field period

You are suggesting that the field name is GL_CLOSE_BALANCE_01 and so

GL_CLOSE_BALANCE_01

45.6

37.4

In which case you would need to do subfield('GL_CLOSE_BALANCE_01', '_',4) AS Period, i;e; treat the field name as a string literal.

Maybe helpful to post a small sample of your data and what you are trying to achieve.

[#3]

I have attached two of the tables that need to link together based on PERIOD (journal_period in the detail transactions table).

In the end, what I am looking for would be:

The user can choose a PERIOD and the Opening balance, balance, and closing balance would change based on that period.

the fields are:

GL_CLOSING_BALANCE_00 (OPENING BALANCE FOR THE YEAR)

GL_BALANCE_01 (THE SUM OF THE TRANSACTIONS FOR PERIOD 01)

GL_CLOSING BALANCE_01 (CLOSING BALANCE FOR PERIOD 01)

THEN SAME FOR PERIOD 02

GL_CLOSING_BALANCE_01 (OPENING BALANCE FOR 02)

GL_BALANCE_02

GL_CLOSING_BALANCE_02

AND SO ON FOR ALL 12 PERIODS.

It needs to link by PERIOD over to the other detail table (gl_tran_lines_ to pull detail info on another sheet of the report)

[#4]

One more thing:  here is what I have put together for each period.  I want it like this but to be able to choose a period rather than have a new sheet for each one.

[Anonymous]

What you need to do it normalize your data.  You might try something like this (undoubtedly many ways to do this)

// load the first spreadsheet

[acct_temp]:

LOAD *

FROM [lib://examples/gl_account_balances table for one account only.xlsx]

(ooxml, embedded labels, table is sheet1);

// normalize it

[acct]:

Load

[GL Account] & '-' & [Balance Type] & '-' & [Current Year] & '-' & [Currency] & '-' & '1' AS myKey

,[Currency]

,[Current Year]

,[Balance Type]

,[GL Account]

,'1' AS Period

,[Period 01 DR] AS DR

,[Period 01 CR] AS CR

,[Closing DR Balance 1] AS Closing_DR_Balance

,[Closing CR Balance 1] AS Closing_CR_Balance

,[Balance Period 1] AS Balance

,[Closing Balance 1] AS Closing_Balance

// other fields as needed

Resident acct_temp;

Load

[GL Account] & '-' & [Balance Type] & '-' & [Current Year] & '-' & [Currency] AS myKey

,[Currency]

,[Current Year]

,[Balance Type]

,[GL Account]

,'2' AS Period

,[Period 02 DR] AS DR

,[Period 02 CR] AS CR

,[Closing DR Balance 2] AS Closing_DR_Balance

,[Closing CR Balance 2] AS Closing_CR_Balance

,[Balance Period 2] AS Balance

,[Closing Balance 2] AS Closing_Balance

Resident acct_temp;

// and so on for all the periods you want

Drop table acct_temp;

// then the second

[trans_temp]:

LOAD *

FROM [lib://examples/gl tran lines-detail for same account number.xlsx]

(ooxml, embedded labels, table is sheet1);

[trans]:

Load

[GL Account] & '-' & [Balance Type] & '-' & [Current Year] & '-' & [Currency] & '-' & [Journal Period] AS myKey

, [Credit Value] AS Credit_Value

// other fields you want

Resident [trans_temp];

Drop table trans_temp;

The tables are now associated by the key that contains the four fields you indicated plus period.

Hope that helps.

[#6]

That makes sense.  Let me try that!  I'll let you know how it turns out!

Thanks a million for your help and time!

[Anonymous]

My solution is a more "brute force" way of what Marcus was helping you with above.

[#8]

Everything worked perfectly for period 01, but when I added period 02 it didn't like it.

Because I already used the field names in the period 01 load, it is creating many synthetic keys.

my code:

GL_ACCOUNT_BALANCES:

LOAD *,

ACCOUNT_NUMBER AS GL_ACCT_KEY

FROM

[$(vQVD)FIN_GL_ACCOUNT_BALANCES.QVD]

(qvd) where ACCOUNT_IDENT='ACTUAL' ;

PERIOD_BALANCES:

load

ACCOUNT_NUMBER AS GL_ACCT_KEY,

ACCOUNT_NUMBER&'|'&CURRENT_YEAR &'|'& GL_ELEMENT_01 &'|'&SYS_CURRENCY_CODE AS GL_TRANS_FULL_KEY,

'01' as PERIOD,

CURRENT_YEAR,

ACCOUNT_NUMBER,

ACCOUNT_IDENT,

SYS_CURRENCY_CODE,

GL_ELEMENT_01 AS COMPANY_CODE,

GL_BALANCE_00 AS OPENING_BALANCE,

GL_CLOSE_BALANCE_01 AS CLOSING_BALANCE,

GL_BALANCE_01 AS PERIOD_TRANS_BALANCE

RESIDENT  GL_ACCOUNT_BALANCES;

LOAD

'02' AS PERIOD,

GL_CLOSE_BALANCE_01 AS OPENING_BALANCE,

GL_BALANCE_02 AS PERIOD_TRANS_BALANCE

RESIDENT  GL_ACCOUNT_BALANCES;

DROP TABLE GL_ACCOUNT_BALANCES;

So everything looks good until I start the load for period 02.

It is saying that I already loaded a field called PERIOD, and also loaded a field called opening balance, etc.

I seem to be missing something between the first load and second load to let it know I'm done with the data load for period 01, but need to keep loading with a new period.

[Anonymous]

Concatenated tables have to have the same schema, i.e field names.  Yours don;t.

[#10]

the problem isn't the field names, but the alias names.....so I've already loaded period as 01, now I'm loading it as 02.  same for opening balance, etc.

If I comment out the period 02 section, it runs fine (see attached).  As soon as I load period, opening balance, gl_account_balances, it fails and says I have already loaded those fields. 

once I add a few fields for the period 02 section it looks like this:

PERIOD_BALANCES:

load

ACCOUNT_NUMBER AS GL_ACCT_KEY,

ACCOUNT_NUMBER&'|'&CURRENT_YEAR &'|'& GL_ELEMENT_01 &'|'&SYS_CURRENCY_CODE AS GL_TRANS_FULL_KEY,

'01' as PERIOD,

CURRENT_YEAR,

ACCOUNT_NUMBER,

ACCOUNT_IDENT,

SYS_CURRENCY_CODE,

GL_ELEMENT_01 AS COMPANY_CODE,

GL_BALANCE_00 AS OPENING_BALANCE,

GL_CLOSE_BALANCE_01 AS CLOSING_BALANCE,

GL_BALANCE_01 AS PERIOD_TRANS_BALANCE

RESIDENT  GL_ACCOUNT_BALANCES;

LOAD

'02' AS PERIOD,

GL_CLOSE_BALANCE_01 AS OPENING_BALANCE,

GL_BALANCE_02 AS PERIOD_TRANS_BALANCE

RESIDENT  GL_ACCOUNT_BALANCES;

DROP TABLE GL_ACCOUNT_BALANCES;

[Anonymous]

You code needs to look like this:

PERIOD_BALANCES:

load

ACCOUNT_NUMBER AS GL_ACCT_KEY,

ACCOUNT_NUMBER&'|'&CURRENT_YEAR &'|'& GL_ELEMENT_01 &'|'&SYS_CURRENCY_CODE AS GL_TRANS_FULL_KEY,

'01' as PERIOD,

CURRENT_YEAR,

ACCOUNT_NUMBER,

ACCOUNT_IDENT,

SYS_CURRENCY_CODE,

GL_ELEMENT_01 AS COMPANY_CODE,

GL_BALANCE_00 AS OPENING_BALANCE,

GL_CLOSE_BALANCE_01 AS CLOSING_BALANCE,

GL_BALANCE_01 AS PERIOD_TRANS_BALANCE

RESIDENT  GL_ACCOUNT_BALANCES;

LOAD

ACCOUNT_NUMBER AS GL_ACCT_KEY,

ACCOUNT_NUMBER&'|'&CURRENT_YEAR &'|'& GL_ELEMENT_01 &'|'&SYS_CURRENCY_CODE AS GL_TRANS_FULL_KEY,

'02' AS PERIOD,

CURRENT_YEAR,

ACCOUNT_NUMBER,

ACCOUNT_IDENT,

SYS_CURRENCY_CODE,

GL_ELEMENT_01 AS COMPANY_CODE,

GL_CLOSE_BALANCE_01 AS OPENING_BALANCE,

GL_BALANCE_02 AS PERIOD_TRANS_BALANCE

RESIDENT  GL_ACCOUNT_BALANCES;

DROP TABLE GL_ACCOUNT_BALANCES;

When you load the data for period 2 you have to have the same field set as in the section for period 1, otherwise Qlik creates a new table for the period 2 data.  And since you have several of the filed names the same, you get all of those synthetic keys.  If you have exactly the same set of fields then Qlik simply appends the period 2 data to the PERIOD_BALANCES table.