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

[stschwarz]

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:

 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

[stschwarz]

See sample screen shot of data below:

 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

[Gysbert_Wassenaar]

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

[stschwarz]

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]

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]

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]

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]

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

[Gysbert_Wassenaar · Accepted solution]

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'

[stschwarz]

Thank you so much!