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);
