23 Replies Latest reply: Apr 26, 2017 12:08 PM by Brian MacDonald RSS

    need to loop through to get period from field

    Sheila Lindner

      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

          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
              Sheila Lindner

              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

                  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
                Sheila Lindner

                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.

                 

                 

                4-17-2017 3-50-47 PM.jpg

                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

                    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
                    Brian MacDonald

                    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
                      Sheila Lindner

                      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
                          Brian MacDonald

                          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
                              Sheila Lindner

                              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
                                  Sheila Lindner

                                  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.

                                  4-25-2017 2-06-51 PM.jpg

                                  • Re: need to loop through to get period from field
                                    Brian MacDonald

                                    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
                                        Sheila Lindner

                                        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
                                            Brian MacDonald

                                            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
                                                Sheila Lindner

                                                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
                                                    Brian MacDonald

                                                    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
                                                        Sheila Lindner

                                                        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.  4-25-2017 7-14-38 PM.jpg

                                                         

                                                        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;

                                                        4-25-2017 7-20-09 PM.jpg

                                                          • Re: need to loop through to get period from field
                                                            Brian MacDonald

                                                            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.