Extract First and Second Transactions Based on Flags

RenukaSasikumar15 — Thu, 13 Mar 2025 07:09:33 GMT

Hi All,

I need assistance with extracting and processing the following data using Qlik Sense Scripting. Below is my current script and the expected output.

ACCOUNT_TRANSACTION:
LOAD
CUSTOMER
DEFAULT_BRANCH,
"Customer Name",
"Cust. Onboarded Date",
"Customer Risk",
POSTING_DATE,
TRANSACTION_TYPE,
MAIN_TYPE,
CHANNEL,
AMOUNT_IN_OMR,

// Flag for Same Day Debit
IF(AMOUNT_IN_OMR < 0 AND Date(Floor(POSTING_DATE)) = Date(Floor(ACCOUNT_OPEN_DATE)), 'Debit Same Day',
IF(AMOUNT_IN_OMR < 0 AND Date(Floor(POSTING_DATE)) > Date(Floor(ACCOUNT_OPEN_DATE)) AND Date(Floor(POSTING_DATE)) <= Date(Floor(ACCOUNT_OPEN_DATE)) + 7, 'Debit Within 1 Week', 'Not Debit Same Day/Within 1 Week')) AS Debit_Flag,

// Flag for Same Day Credit
IF(AMOUNT_IN_OMR > 0 AND Date(Floor(POSTING_DATE)) = Date(Floor(ACCOUNT_OPEN_DATE)), 'Credit Same Day',
IF(AMOUNT_IN_OMR > 0 AND Date(Floor(POSTING_DATE)) > Date(Floor(ACCOUNT_OPEN_DATE)) AND Date(Floor(POSTING_DATE)) <= Date(Floor(ACCOUNT_OPEN_DATE)) + 7, 'Credit Within 1 Week')) AS Credit_Flag

FROM ACCOUNT_TRANSACTION;

Expected Output:

I would like to extract the following fields:

Branch
Customer Number
Customer Name
Customer Risk
Date of opening account
Date of first transaction
First Transaction amount
First Transaction type
Date of 2nd Trans
Second Transaction amount
Second Transaction type

The First Transaction refers to the earliest transaction for the customer, including the transaction date, amount, and type.

The Second Transaction will be the debit transaction (if any), which follows the first transaction for the same customer.

I also want to include the flags Credit Flag and Debit Flag to capture the transaction types.

Re: Extract First and Second Transactions Based on Flags

BrunPierre — Fri, 14 Mar 2025 12:11:14 GMT

Temp:
<Initial Load goes here >
;

Final:
Load CUSTOMER,
Branch,
[Customer Name],
[Customer Risk],
FirstValue([Posting Date]) as [1st Transaction Date],
FirstValue(TRANSACTION_TYPE )as [1st Transaction Type],
FirstValue(AMOUNT_IN_OMR) as [1st Transaction Amt]
Resident Temp
Group By CUSTOMER,Branch,[Customer Name], [Customer Risk];

Left Join
LOAD CUSTOMER,
FirstValue([Posting Date]) as [2nd Transaction Date],
FirstValue(TRANSACTION_TYPE )as [2nd Transaction Type],
FirstValue(AMOUNT_IN_OMR) as [2nd Transaction Amt]
Resident Temp
Where Sign(AMOUNT_IN_OMR) = '-1'
Group By CUSTOMER;

DROP Table Temp;

topic Re: Extract First and Second Transactions Based on Flags in App Development

Extract First and Second Transactions Based on Flags

Re: Extract First and Second Transactions Based on Flags