Help with limited Script

Anonymous — Tue, 22 Apr 2014 02:58:56 GMT

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

Re: Help with limited Script

sujeetsingh — Tue, 22 Apr 2014 03:53:59 GMT

Your script is quite confusing can you please send a sample with small INLINE concepts

Re: Help with limited Script

Anonymous — Tue, 22 Apr 2014 04:08:11 GMT

I'm not sure what you mean by small INLINE concepts, my qlikview document pulls information directly from a live database so I can't pull out a small sample.

Re: Help with limited Script

Greg_Williams — Tue, 22 Apr 2014 04:59:27 GMT

Have you considered using an Mapping Load and Applymap() function?

This might simplify your script and provide more flexibility in the future.

<name of table>:

Mapping load * inline [

in_value, out_value

12345,Rent Code

23456,Rent Code

0,No Code

];

load *,

applymap('<map table name>',<some field>) as <whatever field name>

from source;

Re: Help with limited Script

Anonymous — Tue, 22 Apr 2014 07:54:07 GMT

Hi Greg

I am still quite new to SQL so no I haven't seen that approach but I will have a look into it. Do you think that would make what I am trying to achieve work?

Re: Help with limited Script

Anonymous — Wed, 23 Apr 2014 01:20:03 GMT

Thanks for the input I have found the solution

topic Re: Help with limited Script in QlikView

Help with limited Script

Re: Help with limited Script

Re: Help with limited Script

Re: Help with limited Script

Re: Help with limited Script

Re: Help with limited Script