Insert a copy line in my transaction table

ch_riadh — Wed, 12 Aug 2020 11:22:04 GMT

Thank you, Just to clarify.

I have a transaction table with 8 fields:

TRS_NO	SEQUENCE	TRANS_DATE	TRANS_AMOUNT	BALANCE	AccountNo	TYPE
101	10011	1/13/2020	1000	1000	1001	ABC
102	10021	1/13/2020	2000	2000	1002	XYZ
103	10022	1/15/2020	900	1100	1002	XYZ
104	10012	1/17/2020	400	1400	1001	ABC
105	10023	1/17/2020	1100	0	1002	XYZ
106	10013	1/17/2020	-1600	-200	1001	ABC
107	10024	1/19/2020	200	-200	1002	XYZ
108	10014	1/20/2020	200	0	1001	ABC

I want to fill data for the non transaction date like the table below.

This is the output i want :

TRS_NO	SEQUENCE	SEQUENCE2	TRANS_DATE	TRANS_AMOUNT	BALANCE	BALANCE2	AccountNo	TYPE
101	10011	10011	1/13/2020	1000	1000	1000	1001	ABC
102	10021	10021	1/13/2020	2000	2000	2000	1002	XYZ
NULL	NULL	10011	1/14/2020	NULL	NULL	1000	1001	ABC
NULL	NULL	10021	1/14/2020	NULL	NULL	2000	1002	XYZ
NULL	NULL	10011	1/15/2020	NULL	NULL	1000	1001	ABC
103	10022	10022	1/15/2020	900	1100	1100	1002	XYZ
NULL	NULL	10011	1/16/2020	NULL	NULL	1000	1001	ABC
NULL	NULL	10022	1/16/2020	NULL	NULL	1100	1002	XYZ
102	10012	10012	1/17/2020	400	1400	1400	1001	ABC
103	10013	10013	1/17/2020	-1600	-200	-200	1001	ABC
105	10023	10023	1/17/2020	1100	0	0	1002	XYZ
NULL	NULL	10013	1/18/2020	NULL	NULL	-200	1001	ABC
NULL	NULL	10023	1/18/2020	NULL	NULL	0	1002	XYZ
NULL	NULL	10013	1/19/2020	NULL	NULL	-200	1001	ABC
107	10024	10024	1/19/2020	200	-200	-200	1002	XYZ
104	10014	10014	1/20/2020	200	0	0	1001	ABC

Please find attached Excel with the 2 tables.

With this script it works for one accountno !!

//----------------------

Data:
LOAD

TRANS_AMOUNT,
SEQUENCE,
TRANS_DATE,
BALANCE,
AccountNo

FROM [lib://DATA/HIST.qvd]
(qvd);

T1:
Load *,

if(BALANCE<>Peek(BALANCE) AND SEQUENCE<>Peek(SEQUENCE)
, alt(rangesum(Peek(Group),1),1),Peek(Group)) as Group
Resident Data
Order by TRANS_DATE,SEQUENCE desc;

Drop Table Data;

T2:
Load Group,
date(min(TRANS_DATE)) as MinDate
Resident T1
Group by Group;

Left Join(T1)
Load Group,
MinDate,
date(alt(Peek(MinDate),MinDate+1)-1) as MaxDate
Resident T2
Order by MinDate desc;

Drop Table T2;

Join(T1)
Load
Group,
date(MinDate+IterNo()-1) as TRANS_DATE
resident T1
While MinDate+IterNo()-1<=MaxDate;

Drop Fields MinDate,MaxDate;

Final:
NoConcatenate
Load
TRANS_DATE,
TRANS_AMOUNT,
AccountNo,
SEQUENCE,
BALANCE,

if(IsNull(SEQUENCE),Peek(SEQUENCE2),SEQUENCE) as SEQUENCE2,
if(IsNull(BALANCE),Peek(BALANCE2),BALANCE) as BALANCE2,
if(IsNull(AccountNo),Peek(AccountNo2),AccountNo) as AccountNo2,

Group
Resident T1
Order by Group,TRANS_DATE,SEQUENCE, AccountNo;

Drop Table T1;

//--------------------------------

Thank you

Re: Insert a copy line in my transaction table

Ksrinivasan — Thu, 13 Aug 2020 18:07:10 GMT

hi,

you can add calendar,

Re: Insert a copy line in my transaction table

stevedark — Thu, 13 Aug 2020 18:31:57 GMT

The sort of process you want to be looking at is;

Create a temporary table with all dates (using autogenerate and (today()-rowno()-1))
Create a temporary table with LOAD DISTINCT of your Account Numbers
Left join the Dates onto the Account Numbers - giving a table with all account numbers at all dates
Left join on the initial transaction tables, filling in transactions at dates
Drop the date temporary table
Resident load from the temporary table, ordered by Account, Sequence and Date
Fill blank Balances with:
if(IsNull(Balance) and Account = peek('Acount', -1), peek('Balance', -1), Balance) as Balance

Basically the left joins will create an entry for each account at each date, with multiple rows for transactions on dates. When you do the resident load you can check back the previous row, and pull forward values from those rows, if it is the same account on the previous day and it has a balance.

Hopefully that gives you enough pointers to be able to build out the rest.

Steve

topic Re: Insert a copy line in my transaction table in App Development

Insert a copy line in my transaction table

Re: Insert a copy line in my transaction table

Re: Insert a copy line in my transaction table