Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

stschwarz
New Contributor II

How do you tag a journal entries (parent) based on specific account numbers (children)

I am running into some challenges figuring out how to tag all Journal Entries that contain a specific transaction to an account.

See sample screen shot of data below:JRNL Entry Screenshot.jpg

 If the ACCOUNT field's left 2 digits start with 10, I would like to tag both of the lines related to this JRN ENTRY as 'FLAG'.

Any help would be greatly appreciated.

Thank you,

Steven

 

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

Basically the same as in SQL except Qlik Sense uses field names to do the joining instead of using an ON syntax.

 

Table1:
Select * From A;

Left Join (Table1)

LOAD
    JRNL_ENTRY
    'Treasurey' as TREASURY_FLAG
RESIDENT
    Table1
WHERE
    Left(ACCOUNT,2)='10'


talk is cheap, supply exceeds demand
8 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

Create an extra field when you load the data:

LOAD
    ...some fields...,
    If(Left([ACCOUNT,2) = '10', 1,0) as FLAG,
    ...some more fields...
FROM ...source_table... ;

 

 


talk is cheap, supply exceeds demand
stschwarz
New Contributor II

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

I appreciate the response. I am probably not doing a very good job at explaining the problem. So when I would write a formula like that the record the has the '10' would return the FLAG, however, I would also want the record that does not have a '10' in the account field to return the FLAG because they are both have the same JRNL ENTRY. Not sure if I am explaining it properly.
stschwarz
New Contributor II

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

Basically both records on the screen shot should return the FLAG value because one of the two ACCOUNT's has a '10' and they are both in the same JRNL ENTRY
stschwarz
New Contributor II

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

I was thinking about it from a SQL perspective and would want to use a self join:
Select
A.*,
(Case When Left(A.ACCOUNT,2)='10' Then 'Treasury'
When B.JRNL_ENTRY Is Not Null Then 'Treasury' Else 'Other' END) As Treasury Flag

From A

Left Outer Join B
On
Left (B.ACCOUNT,2)='10'
And A.JRNL_ENTRY = B.JRNL_ENTRY
And A.JRNL_DESCR = B.JRNL_DESCR
And Left(A.ACCOUNT,2) <> '10'

Not sure how to do that in Qlik Sense
stschwarz
New Contributor II

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

I was thinking about it from a SQL perspective and am thinking I could use a self join.

Select
A.*,
(Case When Left(A.ACCOUNT,2)='10' Then 'Treasury'
When B.JRNL_ENTRY Is Not Null Then 'Treasury' Else 'Other' END) As Treasury Flag

From A

Left Outer Join B
On
Left (B.ACCOUNT,2)='10'
And A.JRNL_ENTRY = B.JRNL_ENTRY
And A.JRNL_DESCR = B.JRNL_DESCR
And Left(A.ACCOUNT,2) <> '10'
stschwarz
New Contributor II

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

For those that are interested. Here is the SQL script that resulted in the right output flag:

SELECT
a.*,
(Case When B.[JRN_ENTRY] Is Not Null Then 'Treasury' Else 'Other' END) As Treasury_Flag

From Table A

LEFT OUTER JOIN

(SELECT DISTINCT
CAST([JRN_ENTRY] AS VARCHAR(50)) AS JRN_ENTRY,
CAST([JRN_DESCR] AS VARCHAR(50)) AS JRN_DESCR
FROM Table
WHERE Left(ACCOUNT,2) in ('10','11')
) B
ON
CAST(a.[JRN_ENTRY] AS VARCHAR(100)) = CAST(b.[JRN_ENTRY] AS VARCHAR(100))
And CAST(a.[JRN_DESCR] AS VARCHAR(100)) = CAST(b.[JRN_DESCR] AS VARCHAR(100))
;
MVP & Luminary
MVP & Luminary

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

Basically the same as in SQL except Qlik Sense uses field names to do the joining instead of using an ON syntax.

 

Table1:
Select * From A;

Left Join (Table1)

LOAD
    JRNL_ENTRY
    'Treasurey' as TREASURY_FLAG
RESIDENT
    Table1
WHERE
    Left(ACCOUNT,2)='10'


talk is cheap, supply exceeds demand
stschwarz
New Contributor II

Re: How do you tag a journal entries (parent) based on specific account numbers (children)

Thank you so much!