Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ch_riadh
Partner
Partner

Insert a copy line in my transaction table

Thank you, Just to clarify.

I have a transaction table with 8 fields:

TRS_NOSEQUENCETRANS_DATETRANS_AMOUNTBALANCEAccountNoTYPE
101100111/13/2020100010001001ABC
102100211/13/2020200020001002XYZ
103100221/15/202090011001002XYZ
104100121/17/202040014001001ABC
105100231/17/2020110001002XYZ
106100131/17/2020-1600-2001001ABC
107100241/19/2020200-2001002XYZ
108100141/20/202020001001ABC


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

This is the output i want :

TRS_NOSEQUENCESEQUENCE2TRANS_DATETRANS_AMOUNTBALANCEBALANCE2AccountNoTYPE
10110011100111/13/20201000100010001001ABC
10210021100211/13/20202000200020001002XYZ
NULLNULL100111/14/2020NULLNULL10001001ABC
NULLNULL100211/14/2020NULLNULL20001002XYZ
NULLNULL100111/15/2020NULLNULL10001001ABC
10310022100221/15/2020900110011001002XYZ
NULLNULL100111/16/2020NULLNULL10001001ABC
NULLNULL100221/16/2020NULLNULL11001002XYZ
10210012100121/17/2020400140014001001ABC
10310013100131/17/2020-1600-200-2001001ABC
10510023100231/17/20201100001002XYZ
NULLNULL100131/18/2020NULLNULL-2001001ABC
NULLNULL100231/18/2020NULLNULL01002XYZ
NULLNULL100131/19/2020NULLNULL-2001001ABC
10710024100241/19/2020200-200-2001002XYZ
10410014100141/20/2020200001001ABC

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

2 Replies
Ksrinivasan
Specialist
Specialist

hi,

you can add calendar,

Ksrinivasan_0-1597341891355.png

 

 

stevedark
MVP
MVP

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