4 Replies Latest reply: Feb 28, 2017 7:48 AM by swarnendu haldar RSS

    Problem In Load Time

    swarnendu haldar

      Hello Guys Good Morning all,

      I am facing a problem in fetching data from sap to qlikview...

      my server is 2008r2 and ram in 64gb..

      when i will try to get data from a custom table then my server consume 100% ram and server is going to hang ...

      And most importantly script execution bar showing me nothing blank and also nothing error  and after some time this server are totally hang....please help me if any one who face this problem or solve this problem...

        • Re: Problem In Load Time
          Rahul Pawar

          Hello Swarnendu,

           

          Below are some pointers which will help you to further analyze the issue:

          1. Restrict the data by applying some filter condition (fetch subset of data and check the reload time).

          2. Identify no. of records present in the base table and define the data load strategy accordinly

          3. If base table is transaction table then try to implement incremental load

          https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/QVD_Incremental.htm

           

          Hope this will be helpful.

           

          Regards!

          Rahul

            • Re: Problem In Load Time
              swarnendu haldar

              Thanks For Your quick reply i will share with you my code please check it....

              1. Its not showing me any reload time only show me only  start time

              2.I think its not fetch any data from sap table...

               

              here is the code.....

               

              Stock_Ageing_Temp:

              Load

              [Field0_Material] As %Material,

              [Field1_Plant] As %Plant,

              [Field2_Stor Loc.] As %StorageLocation,

              [Field3_Mat Type] As @_MaterialType,

              [Field4_SBU] As %SBU,

              [Field5_SO No.] As @_SO_No,

              [Field6_SO Item] As @_SO_Item,

              [Field7_Delv. Date] As @_DeleveryDate,

              [Field8_Status] As @_Status,

              [Field9_Customer Code] As %CustomerCode,

              Num#([Field10_Stk Qty]) As #_StockQty,

              Num#([Field11_Stk Value]) As #_StockVal,

              Num#([Field12_Stk Qty(0-30)days]) As #_StockQty_0_30_Days,

              Num#([Field13_Stk Val(0-30)days]) As #_StockVal_0_30_Days,

              Num#([Field14_Stk Qty(31-60)days]) As #_StockQty_31_60_Days,

              Num#([Field15_Stk Val(31-60)days]) As #_StockVal_31_60_Days,

              Num#([Field16_Stk Qty(61-90)days]) As #_StockQty_61_90_Days,

              Num#([Field17_Stk Val(61-90)days]) As #_StockVal_61_90_Days,

              Num#([Field18_Stk Qty(91-120)days]) As #_StockQty_91_120_Days,

              Num#([Field19_Stk Val(91-120)days]) As #_StockVal_91_120_Days,

              Num#([Field20_Stk Qty(121-150)days]) As #_StockQty_121_150_Days,

              Num#([Field21_Stk Val(121-150)days]) As #_StockVal_121_150_Days,

              Num#([Field22_Stk Qty(151-180)days]) As #_StockQty_151_180_Days,

              Num#([Field23_Stk Val(151-180)days]) As #_StockVal_151_180_Days,

              Num#([Field24_Stk Qty(181-365)days]) As #_StockQty_181_365_Days,

              Num#([Field25_Stk Val(181-365)days]) As #_StockVal_181_365_Days,

              Num#([Field26_Stk Qty More than 1 year]) As #_StockQty_GT_365_Days,

              Num#([Field27_Stk Val More than 1 year]) As #_StockVal_GT_365_Days,

              Num#([Field28_Stk Qty More than 2 year]) As #_StockQty_GT_2_Year,

              Num#([Field29_Stk Val More than 2 year]) As #_StockVal_GT_2_Year,

              Num#([Field30_Stk Qty More than 3 year]) As #_StockQty_GT_3_Year,

              Num#([Field31_Stk Val More than 3 year]) As #_StockVal_GT_3_Year,

              //[Field32_]

              Date(Today()-1,'DD/MM/YYYY') As @_Last_Execute_Date

              ;

              //Select SAPREPORT (REPORT (ZMM_STLOC_STOCK_QV), VARIANT (ALL), ROWS_PER_RECORD (1), FIELD_DELIMITER_POSITIONS (0, 20, 26, 36, 45, 49, 58, 66, 77, 87, 101, 115, 130, 148, 166, 185, 204, 223, 242, 262, 282, 303, 324, 345, 366, 387, 408, 433, 458, 483, 508, 533, 558), SKIP (P (0, "┌────────────────────────┬─────────┐"), P (0, "!Data statistics         !Number of!"), P (0, "├────────────────────────┼─────────┤"), P (0, "!Records passed          !   24,537!"), P (0, "!Calculated total records!        1!"), P (0, "└────────────────────────┴─────────┘"), P (0, "┌───────────────────┬─────┬─────────┬────────┬───┬────────┬───────┬──────────┬─────────┬─────────────┬─────────────┬──────────────┬─────────────────┬─────────────────┬──────────────────┬──────────────────┬──────────────────┬──────────────────┬───────────────────┬───────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────────┬────────────────────────┬────────────────────────┬──────────"), P (0, "├───────────────────┼─────┼─────────┼────────┼───┼────────┼───────┼──────────┼─────────┼─────────────┼─────────────┼──────────────┼─────────────────┼─────────────────┼──────────────────┼──────────────────┼──────────────────┼──────────────────┼───────────────────┼───────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────────┼────────────────────────┼────────────────────────┼────────────────"), P (0, "└───────────────────┴─────┴─────────┴────────┴───┴────────┴───────┴──────────┴─────────┴─────────────┴─────────────┴──────────────┴─────────────────┴─────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┴───────────────────┴───────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴─────────────"), P (0, "!*                  !     !         !        !   !        !       !          !         !             !             !              !                 !                 !                  !                  !                  !                  !                   !                   !                    !                    !                    !                    !                    !                    !            763,439.324 !          83,002,327.85 !            380,923.881 !          29,966,430.08 !            787,017.880 !          42,822,554.65 !")), HEADER (P (0, "! Material          !Plant!Stor Loc.!Mat Type!SBU!SO No.  !SO Item!Delv. Date!Status   !Customer Code!      Stk Qty!     Stk Value!Stk Qty(0-30)days!Stk Val(0-30)days!Stk Qty(31-60)days!Stk Val(31-60)days!Stk Qty(61-90)days!Stk Val(61-90)days!Stk Qty(91-120)days!Stk Val(91-120)days!Stk Qty(121-150)days!Stk Val(121-150)days!Stk Qty(151-180)days!Stk Val(151-180)days!Stk Qty(181-365)days!Stk Val(181-365)days!Stk Qty More than 1 year!Stk Val More than 1 year!Stk Qty More than 2 year!Stk Val More than 2 year!Stk Qty More than 3 year!Stk Val More than 3 year!")));

              Select SAPREPORT (REPORT (ZMM_STLOC_STOCK_QV), VARIANT (ALL), ROWS_PER_RECORD (1), FIELD_DELIMITER_POSITIONS (0, 20, 26, 36, 45, 49, 58, 66, 77, 87, 101, 113, 128, 146, 164, 183, 202, 221, 240, 260, 280, 301, 322, 343, 364, 385, 406, 431, 456, 481, 506, 531, 556), SKIP (P (0, "┌────────────────────────┬─────────┐"), P (0, "!Data statistics         !Number of!"), P (0, "├────────────────────────┼─────────┤"), P (0, "!Records passed          !   24,002!"), P (0, "!Calculated total records!        1!"), P (0, "└────────────────────────┴─────────┘"), P (0, "┌───────────────────┬─────┬─────────┬────────┬───┬────────┬───────┬──────────┬─────────┬─────────────┬───────────┬──────────────┬─────────────────┬─────────────────┬──────────────────┬──────────────────┬──────────────────┬──────────────────┬───────────────────┬───────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────────┬────────────────────────┬────────────────────────┬────────────"), P (0, "├───────────────────┼─────┼─────────┼────────┼───┼────────┼───────┼──────────┼─────────┼─────────────┼───────────┼──────────────┼─────────────────┼─────────────────┼──────────────────┼──────────────────┼──────────────────┼──────────────────┼───────────────────┼───────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────────┼────────────────────────┼────────────────────────┼──────────────────"), P (0, "└───────────────────┴─────┴─────────┴────────┴───┴────────┴───────┴──────────┴─────────┴─────────────┴───────────┴──────────────┴─────────────────┴─────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┴───────────────────┴───────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴───────────────"), P (0, "!*                  !     !         !        !   !        !       !          !         !             !           !              !                 !                 !                  !                  !                  !                  !                   !                   !                    !                    !                    !                    !                    !                    !            776,341.657 !          79,819,506.26 !            378,888.841 !          33,122,594.28 !            792,306.740 !          42,887,691.76 !")), HEADER (P (0, "! Material          !Plant!Stor Loc.!Mat Type!SBU!SO No.  !SO Item!Delv. Date!Status   !Customer Code!    Stk Qty!     Stk Value!Stk Qty(0-30)days!Stk Val(0-30)days!Stk Qty(31-60)days!Stk Val(31-60)days!Stk Qty(61-90)days!Stk Val(61-90)days!Stk Qty(91-120)days!Stk Val(91-120)days!Stk Qty(121-150)days!Stk Val(121-150)days!Stk Qty(151-180)days!Stk Val(151-180)days!Stk Qty(181-365)days!Stk Val(181-365)days!Stk Qty More than 1 year!Stk Val More than 1 year!Stk Qty More than 2 year!Stk Val More than 2 year!Stk Qty More than 3 year!Stk Val More than 3 year!")));

               

               

              Stock_Ageing:

              LOAD %Material,

                   %Plant,

                   %StorageLocation,

                   @_MaterialType,

                   %SBU,

                   @_SO_No,

                   @_SO_Item,

                   @_DeleveryDate,

                   @_Status,

                   %CustomerCode,

                   #_StockQty,

                   #_StockVal,

                   #_StockQty_0_30_Days,

                   #_StockVal_0_30_Days,

                   #_StockQty_31_60_Days,

                   #_StockVal_31_60_Days,

                   #_StockQty_61_90_Days,

                   #_StockVal_61_90_Days,

                   #_StockQty_91_120_Days,

                   #_StockVal_91_120_Days,

                   #_StockQty_121_150_Days,

                   #_StockVal_121_150_Days,

                   #_StockQty_151_180_Days,

                   #_StockVal_151_180_Days,

                   #_StockQty_181_365_Days,

                   #_StockVal_181_365_Days,

                   #_StockQty_GT_365_Days,

                   #_StockVal_GT_365_Days,

                   #_StockQty_GT_2_Year,

                   #_StockVal_GT_2_Year,

                   #_StockQty_GT_3_Year,

                   #_StockVal_GT_3_Year,

                   @_Last_Execute_Date,

                   1 As WD

              Resident Stock_Ageing_Temp

              Where %Material<>'*' And %Material<>'Records passed'

              ;

               

               

              STORE Stock_Ageing into D:\Qlik@Tega\Data\RawQvds\STOCK_AGEING_DAILY.qvd;

              DROP Table Stock_Ageing;

              DROP Table Stock_Ageing_Temp;

               

               

              //*****

               

              //EXIT Script;

                • Re: Problem In Load Time
                  Rahul Pawar

                  Hello Swarnendu,

                   

                  When it comes to getting data from SAP Reports I am Lehman . BTW, I will start my analysis with below given query. You can check whether SAP Report - ZMM_STLOC_STOCK_QV is working fine or not (has any performance issues?) . This will lead you to actual issue.

                   

                  Select SAPREPORT (REPORT (ZMM_STLOC_STOCK_QV), VARIANT (ALL), ROWS_PER_RECORD (1), FIELD_DELIMITER_POSITIONS (0, 20, 26, 36, 45, 49, 58, 66, 77, 87, 101, 113, 128, 146, 164, 183, 202, 221, 240, 260, 280, 301, 322, 343, 364, 385, 406, 431, 456, 481, 506, 531, 556), SKIP (P (0, "┌────────────────────────┬─────────┐"), P (0, "!Data statistics        !Number of!"), P (0, "├────────────────────────┼─────────┤"), P (0, "!Records passed          !  24,002!"), P (0, "!Calculated total records!        1!"), P (0, "└────────────────────────┴─────────┘"), P (0, "┌───────────────────┬─────┬─────────┬────────┬───┬────────┬───────┬──────────┬─────────┬─────────────┬───────────┬──────────────┬─────────────────┬─────────────────┬──────────────────┬──────────────────┬──────────────────┬──────────────────┬───────────────────┬───────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────┬────────────────────────┬────────────────────────┬────────────────────────┬────────────"), P (0, "├───────────────────┼─────┼─────────┼────────┼───┼────────┼───────┼──────────┼─────────┼─────────────┼───────────┼──────────────┼─────────────────┼─────────────────┼──────────────────┼──────────────────┼──────────────────┼──────────────────┼───────────────────┼───────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────┼────────────────────────┼────────────────────────┼────────────────────────┼──────────────────"), P (0, "└───────────────────┴─────┴─────────┴────────┴───┴────────┴───────┴──────────┴─────────┴─────────────┴───────────┴──────────────┴─────────────────┴─────────────────┴──────────────────┴──────────────────┴──────────────────┴──────────────────┴───────────────────┴───────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────┴────────────────────────┴────────────────────────┴────────────────────────┴───────────────"), P (0, "!*                  !    !        !        !  !        !      !          !        !            !          !              !                !                !                  !                  !                  !                  !                  !                  !                    !                    !                    !                    !                    !                    !            776,341.657 !          79,819,506.26 !            378,888.841 !          33,122,594.28 !            792,306.740 !          42,887,691.76 !")), HEADER (P (0, "! Material          !Plant!Stor Loc.!Mat Type!SBU!SO No.  !SO Item!Delv. Date!Status  !Customer Code!    Stk Qty!    Stk Value!Stk Qty(0-30)days!Stk Val(0-30)days!Stk Qty(31-60)days!Stk Val(31-60)days!Stk Qty(61-90)days!Stk Val(61-90)days!Stk Qty(91-120)days!Stk Val(91-120)days!Stk Qty(121-150)days!Stk Val(121-150)days!Stk Qty(151-180)days!Stk Val(151-180)days!Stk Qty(181-365)days!Stk Val(181-365)days!Stk Qty More than 1 year!Stk Val More than 1 year!Stk Qty More than 2 year!Stk Val More than 2 year!Stk Qty More than 3 year!Stk Val More than 3 year!")));

                   


                  Hope this will be helpful.

                   

                  Regards!

                  Rahul