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

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

stschwarz — Tue, 08 Jan 2019 17:42:30 GMT

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

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

Gysbert_Wassenaar — Tue, 08 Jan 2019 17:57:38 GMT

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

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

stschwarz — Tue, 08 Jan 2019 18:32:15 GMT

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.

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

stschwarz — Tue, 08 Jan 2019 19:09:16 GMT

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

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

stschwarz — Tue, 08 Jan 2019 22:41:25 GMT

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

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

stschwarz — Tue, 08 Jan 2019 22:43:23 GMT

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'

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

stschwarz — Wed, 09 Jan 2019 13:24:26 GMT

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

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

Gysbert_Wassenaar — Wed, 09 Jan 2019 14:14:40 GMT

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'

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

stschwarz — Wed, 09 Jan 2019 14:44:46 GMT

Thank you so much!