5 Replies Latest reply: Apr 22, 2014 9:20 PM by Bella Mae RSS

    Help with limited Script

    Bella Mae

      Hi All

        

      I have the below script in my Qlikview document the FinancialTransactions table pulls through all GL line items (or so I thought) it turns out it does not pull through line items that do not have a PMA_CODE assigned to them.  I need all the line items including those without a PMA_CODE.  The left join below was my attempt at pulling this information through but it is not working as the PMA_CODE is part of the composite key and not a field in and of itself. I cannot add the PMA_CODE into the original table by itself as it makes the output inaccurate (has a tendency to multiply the figures)

        

      Any ideas would be helpful.

       

      Code 

      FinancialTransactions:
      LOAD EN_DATE as Transaction_Date,
      date(EN_DATE) as TransactionDate,
      num(EN_DATE) as %TransactionDateDateKey,
      EN_CODE,
      EN_BODY as [Entry Desc],
      GLT_DESC as [Entry Type],
      [CR Name],
      [DR Name],
      F_EntryType_SEQ,
      if(F_EntryType_SEQ='4' or F_EntryType_SEQ='5' or F_EntryType_SEQ='6' or F_EntryType_SEQ='7',[DR
      Name]
      , if(F_EntryType_SEQ='8' or F_EntryType_SEQ='9' or F_EntryType_SEQ='10' or F_EntryType_SEQ='11',[CR
      Name]
      , Null())) as DRCR,

      Description as [Entry Type Desc],
      CODE as [BU Code],
      PMP_CODE&PMA_CODE&CODE as FinCompKey,
      IT_DEBIT as Debit,
      IT_CREDIT as Credit,
      IT_DESCRIPTION as [Item Desc],
      IT_DEBIT - IT_CREDIT as Amount,
      if (AC_CODE='830300-3' or AC_CODE='830000' or AC_CODE='830015-2' or AC_CODE='830015-3',(IT_DEBIT - IT_CREDIT),0) as [Opex Codes],
      if (AC_CODE='835360-1'or AC_CODE='835360-2' or AC_CODE='835365',(IT_DEBIT - IT_CREDIT),0) as [Rent Codes],
      if (AC_CODE='835361',(IT_DEBIT),0) as [IC Rent Codes],
      IT_SEQ,
      AC_CODE as GL_Code,
      AC_NAME as GL_Name,
      AC_CODE &' - ' & AC_NAME as ChartofAccts
      where num(EN_DATE) > num($(varFirstTransactionDate));

      SQL SELECT
      F_Entry.EN_CODE,
      F_Entry.EN_DATE,
      F_Account.AC_CODE,
      F_Account.AC_NAME,
      fn_CurrentBusinessUnit.CODE,

      F_Pm_Property.PMP_CODE,
      F_Pm_Area.PMA_CODE,
      F_Item.IT_DEBIT,
      F_Item.IT_CREDIT,
      F_GL_Type.GLT_DESC,
      F_GL_Type.GLT_CODE,
      F_Item.IT_SEQ,
      F_Item.IT_DESCRIPTION,
      F_Entry.EN_BODY,
      F_EntryType.Code,
      F_EntryType.F_EntryType_SEQ,
      F_Contact.CO_NAME as [CR Name],

      F_Contact1.CO_NAME as [DR Name],
      F_EntryType.Description

      from F_Item
      LEFT OUTER JOIN F_Account ON F_Item.AC_SEQ = F_Account.AC_SEQ
      LEFT OUTER JOIN F_Entry ON F_Item.EN_SEQ = F_Entry.EN_SEQ
      LEFT OUTER JOIN F_Pm_Property ON F_Item.PMP_SEQ = F_Pm_Property.PMP_SEQ
      LEFT OUTER JOIN F_Pm_Area ON F_Item.PMA_SEQ = F_Pm_Area.PMA_SEQ
      LEFT OUTER JOIN F_BusinessUnit ON F_Item.F_BusinessUnit_SEQ = F_BusinessUnit.F_BusinessUnit_SEQ
      LEFT OUTER JOIN F_GL_Type ON F_Account.GLT_SEQ = F_GL_Type.GLT_SEQ
      LEFT OUTER JOIN F_BusinessUnitStatus ON F_BusinessUnit.F_BusinessUnitStatus_SEQ= F_BusinessUnitStatus.F_BusinessUnitStatus_SEQ
      LEFT OUTER JOIN fn_CurrentBusinessUnit() ON F_Pm_Area.PMA_SEQ =fn_CurrentBusinessUnit.PMA_SEQ
      LEFT OUTER JOIN F_EntryType ON F_Entry.F_EntryType_SEQ =F_EntryType.F_EntryType_SEQ
      LEFT OUTER JOIN F_Cr_Detail ON F_Entry.CR_SEQ = F_Cr_Detail.CR_SEQ
      LEFT OUTER JOIN F_Dr_Detail ON F_Entry.DR_SEQ = F_Dr_Detail.DR_SEQ
      LEFT OUTER JOIN F_Contact F_Contact1 ON F_Dr_Detail.CO_SEQ = F_Contact1.CO_SEQ
      LEFT OUTER JOIN F_Contact ON F_Cr_Detail.CO_SEQ = F_Contact.CO_SEQ
      where (GLT_CODE = '1' or GLT_CODE = '2');


      left join (FinancialTransactions)
      LOAD Transaction_Date,
           TransactionDate,
          
      %TransactionDateDateKey,
          
      EN_CODE,
          
      [Entry Desc],
          
      [Entry Type],
          
      [CR Name],
          
      [DR Name],
          
      F_EntryType_SEQ,
          
      [Entry Type Desc],
          
      [BU Code],
          
      FinCompKey,
          
      Debit,
          
      Credit,
          
      [Item Desc],
          
      Amount,
          
      [Opex Codes],
          
      [Rent Codes],
          
      [IC Rent Codes],
          
      IT_SEQ,
          
      GL_Code,
          
      GL_Name,
          
      ChartofAccts

      resident FinancialTransactions
      where isnull(PMA_CODE);