May 6, 2015 at 2:28 am
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
May 6, 2015 at 5:30 am
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