Different levels to make table flat

  • Im trying to fill my table in different levels (hierarchy). The different levels are not properly processed. All layers now have the same value. It is intended that the layers are beaten flat. This implies multiple joins in order to be able to get the different levels. 'Ledgertableinterval' could be used to determine the hierarchy, but I did not come all the way out ...

    So my suggestion is to fill with 1 character level 1, level 2 with 2 character and so on. If a level is not available anymore than filling up with higher level. For this you can use the replace function.

    The DIM that I use;

    USE [NJ]

    GO

    /****** Object: Table [DIM].[FA] Script Date: 05/06/2015 09:57:24 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [DIM].[FA](

    [FACC_WID] [int] IDENTITY(1,1) NOT NULL,

    [FACC_BKEY] [nvarchar](25) NOT NULL,

    [FACC_CODE] [nvarchar](20) NOT NULL,

    [FACC_DESC] [nvarchar](60) NULL,

    [FACC_CODE_DESC] [nvarchar](83) NULL,

    [FACC_DESC_CODE] [nvarchar](83) NULL,

    [FACC_H1_L5_CODE] [nvarchar](20) NULL,

    [FACC_H1_L5_DESC] [nvarchar](60) NULL,

    [FACC_H1_L5_CODE_DESC] [nvarchar](83) NULL,

    [FACC_H1_L5_DESC_CODE] [nvarchar](83) NULL,

    [FACC_H1_L4_CODE] [nvarchar](20) NULL,

    [FACC_H1_L4_DESC] [nvarchar](60) NULL,

    [FACC_H1_L4_CODE_DESC] [nvarchar](83) NULL,

    [FACC_H1_L4_DESC_CODE] [nvarchar](83) NULL,

    [FACC_H1_L3_CODE] [nvarchar](20) NULL,

    [FACC_H1_L3_DESC] [nvarchar](60) NULL,

    [FACC_H1_L3_CODE_DESC] [nvarchar](83) NULL,

    [FACC_H1_L3_DESC_CODE] [nvarchar](83) NULL,

    [FACC_H1_L2_CODE] [nvarchar](20) NULL,

    [FACC_H1_L2_DESC] [nvarchar](60) NULL,

    [FACC_H1_L2_CODE_DESC] [nvarchar](83) NULL,

    [FACC_H1_L2_DESC_CODE] [nvarchar](83) NULL,

    [FACC_H1_L1_CODE] [nvarchar](20) NULL,

    [FACC_H1_L1_DESC] [nvarchar](60) NULL,

    [FACC_H1_L1_CODE_DESC] [nvarchar](83) NULL,

    [FACC_H1_L1_DESC_CODE] [nvarchar](83) NULL,

    [FACC_TYPE_CODE] [nvarchar](20) NULL,

    [FACC_TYPE_DESC] [nvarchar](25) NULL,

    [FACC_TYPE_CODE_DESC] [nvarchar](48) NULL,

    [FACC_TYPE_DESC_CODE] [nvarchar](48) NULL,

    [FACC_CATEGORY_CODE] [nvarchar](20) NULL,

    [FACC_CATEGORY_DESC] [nvarchar](60) NULL,

    [FACC_CATEGORY_CODE_DESC] [nvarchar](83) NULL,

    [FACC_CATEGORY_DESC_CODE] [nvarchar](83) NULL,

    [FACC_ENTITIY_CODE] [nvarchar](4) NULL,

    [FACC_ENTITY_DESC] [nvarchar](40) NULL,

    [FACC_ENTITIY_CODE_DESC] [nvarchar](47) NULL,

    [FACC_ENTITY_DESC_CODE] [nvarchar](47) NULL,

    [INSERTED_DATE] [datetime] NULL,

    [UPDATED_DATE] [datetime] NULL,

    [INDICATOR_CURRENT] [bit] NULL,

    [START_DATE] [datetime] NULL,

    [END_DATE] [datetime] NULL,

    CONSTRAINT [PK_DIM_FA] PRIMARY KEY CLUSTERED

    (

    [FACC_WID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DIM_TABLES]

    ) ON [DIM_TABLES]

    GO

    For every level (FACC_H1_L1_CODE and FACC_H1_L1_DESC) I need a calculation like (If null level -1).

    There is a table that I can use, but I failed to get it working. Below the table that I can use (Ledger Table Interval).

    USE [NA]

    GO

    /****** Object: Table [xxx].[LTL] Script Date: 05/06/2015 10:12:44 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [xxx].[LTI](

    [RECVERSION] [int] NULL,

    [RECID] [bigint] NULL,

    [DATAAREAID] [nvarchar](4) NULL,

    [ACCOUNTTABLEID] [int] NULL,

    [ACCOUNTRECID] [bigint] NULL,

    [DEL_LINENUM] [numeric](28, 12) NULL,

    [FROMACCOUNT] [nvarchar](20) NULL,

    [TOACCOUNT] [nvarchar](20) NULL,

    [REVERSESIGN] [int] NULL,

    [Inserted_Date] [datetime] NULL

    ) ON [PRIMARY]

    GO

    The DIM.FA (Results)

    FACC_BKEY FACC_CODE FACC_DESC FACC_CODE_DESC FACC_DESC_CODE FACC_H1_L5_CODE FACC_H1_L5_DESC FACC_H1_L5_CODE_DESC FACC_H1_L5_DESC_CODE FACC_H1_L4_CODE FACC_H1_L4_DESC FACC_H1_L4_CODE_DESC FACC_H1_L4_DESC_CODE FACC_H1_L3_CODE FACC_H1_L3_DESC FACC_H1_L3_CODE_DESC FACC_H1_L3_DESC_CODE FACC_H1_L2_CODE FACC_H1_L2_DESC FACC_H1_L2_CODE_DESC FACC_H1_L2_DESC_CODE FACC_H1_L1_CODE FACC_H1_L1_DESC FACC_H1_L1_CODE_DESC FACC_H1_L1_DESC_CODE FACC_TYPE_CODE FACC_TYPE_DESC FACC_TYPE_CODE_DESC FACC_TYPE_DESC_CODE FACC_CATEGORY_CODE FACC_CATEGORY_DESC FACC_CATEGORY_CODE_DESC FACC_CATEGORY_DESC_CODE FACC_ENTITIY_CODE FACC_ENTITY_DESC FACC_ENTITIY_CODE_DESC FACC_ENTITIY_DESC_CODE

    ------------------------- -------------------- ------------------------------------------------------------ ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- -------------------- ------------------------------------------------------------ ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- -------------------- ------------------------------------------------------------ ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- -------------------- ------------------------------------------------------------ ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- -------------------- ------------------------------------------------------------ ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- -------------------- ------------------------------------------------------------ ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- -------------- ---------------- ----------------------- ----------------------- -------------------- ------------------------------------------------------------ ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- ----------------- ---------------------------------------- ----------------------------------------------- -----------------------------------------------

    ace_101100 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100) 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100) 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100) 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100) 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100) 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100) 3 Balance Sheet 3 - (3) CASH CASH CASH - CASH CASH (CASH) ace ACE ace (ace)

    ace_101100 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100) 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100) 101100 COMPTE COURANT PRIVE 101100 - COMPTE COURANT PRIVE COMPTE COURANT PRIVE (101100)

    NA.LTI

    RECVERSION RECID DATAAREAID ACCOUNTTABLEID ACCOUNTRECID DEL_LINENUM FROMACCOUNT TOACCOUNT REVERSESIGN Inserted_Date

    ----------- -------------------- ---------- -------------- -------------------- --------------------------------------- -------------------- -------------------- ----------- -----------------------

    1 5637146854 IMA 221 5637195093 0.000000000000 691 691z 0 2015-05-06 09:22:49.527

    1 5637146855 IMA 221 5637195095 0.000000000000 6920 6920z 0 2015-05-06 09:22:49.527

    1 5637146856 IMA 221 5637195097 0.000000000000 6921 6921z 0 2015-05-06 09:22:49.527

    1 5637146857 IMA 221 5637195099 0.000000000000 693 693z 0 2015-05-06 09:22:49.527

    1 5637146858 IMA 221 5637195101 0.000000000000 694 694z 0 2015-05-06 09:22:49.527

    1 5637146859 IMA 221 5637195102 0.000000000000 694 696z 0 2015-05-06 09:22:49.527

    1 5637146860 IMA 221 5637195104 0.000000000000 695 695z 0 2015-05-06 09:22:49.527

    1 5637146861 IMA 221 5637195106 0.000000000000 696 696z 0 2015-05-06 09:22:49.527

  • It must be like this;

    Level 1 Level 2

    Level 3 Level 4

    Level 5

    1 10

    100 1001

    100100

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply