Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
bellamae
Valued Contributor

Help with limited Script

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

1 Solution

Accepted Solutions
bellamae
Valued Contributor

Re: Help with limited Script

Thanks for the input I have found the solution

5 Replies
sujeetsingh
Honored Contributor III

Re: Help with limited Script

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

bellamae
Valued Contributor

Re: Help with limited Script

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.

Employee
Employee

Re: Help with limited Script

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;

bellamae
Valued Contributor

Re: Help with limited Script

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?

bellamae
Valued Contributor

Re: Help with limited Script

Thanks for the input I have found the solution

Community Browser