topic Re: need to loop through to get period from field in App Development

need to loop through to get period from field

Tue, 11 Apr 2017 01:27:28 GMT

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.

Re: need to loop through to get period from field

marcus_sommer — Thu, 13 Apr 2017 05:38:54 GMT

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

Re: need to loop through to get period from field

Thu, 13 Apr 2017 23:41:24 GMT

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.

Re: need to loop through to get period from field

marcus_sommer — Mon, 17 Apr 2017 15:30:21 GMT

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

Re: need to loop through to get period from field

Mon, 17 Apr 2017 21:06:35 GMT

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;

Re: need to loop through to get period from field

marcus_sommer — Tue, 18 Apr 2017 07:09:34 GMT

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

Re: need to loop through to get period from field

Anonymous — Tue, 18 Apr 2017 17:59:24 GMT

In a Load script you could also do:

Load

, ...

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

;

Where myField has values like GL_CLOSING_BALANCE_01

Re: need to loop through to get period from field

Tue, 18 Apr 2017 21:20:53 GMT

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

Re: need to loop through to get period from field

Anonymous — Tue, 18 Apr 2017 21:45:36 GMT

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.

Re: need to loop through to get period from field

Mon, 24 Apr 2017 16:57:51 GMT

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.

Re: need to loop through to get period from field

Anonymous — Mon, 24 Apr 2017 17:41:42 GMT

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.

Re: need to loop through to get period from field

Tue, 25 Apr 2017 19:02:55 GMT

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)

Re: need to loop through to get period from field

Tue, 25 Apr 2017 19:09:42 GMT

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.

Re: need to loop through to get period from field

Anonymous — Tue, 25 Apr 2017 20:29:22 GMT

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.

Re: need to loop through to get period from field

Tue, 25 Apr 2017 20:32:22 GMT

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

Thanks a million for your help and time!

Re: need to loop through to get period from field

Anonymous — Tue, 25 Apr 2017 20:36:58 GMT

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

Re: need to loop through to get period from field

Tue, 25 Apr 2017 23:11:49 GMT

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.

Re: need to loop through to get period from field

Anonymous — Tue, 25 Apr 2017 23:21:50 GMT

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

Re: need to loop through to get period from field

Wed, 26 Apr 2017 00:21:41 GMT

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;

Re: need to loop through to get period from field

Anonymous — Wed, 26 Apr 2017 13:37:21 GMT

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.