Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
RenukaSasikumar15
Contributor
Contributor

Extract First and Second Transactions Based on Flags

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.

Labels (1)
1 Reply
BrunPierre
Partner - Master II
Partner - Master II

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;