Qlikview - how to associate account number

[loke2014]

Hi,

I want to use Qlikview to be able to associate all relevant account  eg. csv file as below:

date,amount,name, from, to

25-02-2018,100.00,Angela, 5196030002, 5196030003

26-02-2018,100.00,Michael, 5196030003, 5196030004

27-02-2018,100.00,Tom, 5196030004, 5196030064

28-02-2018,200.00,Kim, 5196030005, 5196030065

If I search for account number 5196030002, the expected search result should return the following:

25-02-2018,100.00,Angela, 5196030002, 5196030003

26-02-2018,100.00,Michael, 5196030003, 5196030004

27-02-2018,100.00,Tom, 5196030004, 5196030064

This is to trace the money has been transferred from Angela to Tom.

Any help is much appreciated.

[MarcoWedel]

is it just the relation between accounts you're looking for or does it also depend of the transferred amount?

[loke2014]

Hi Marco,

For the time being it is relation. I have tried to lump them into a single account number field, but this will duplicate a lot of identical rows excessively.

[MarcoWedel]

maybe one solution could be something like:

tabTransact:
LOAD *,
    'Name'&Num(Mid(From,2)) as Name;
LOAD RecNo() as TransactID,
    DayName(Today()-Rand()*100) as Date,
    100+Ceil(Rand()*10) as From,
    100+Ceil(Rand()*10) as To
AutoGenerate 500;

tabTransactPathTemp1:
LOAD '' as TransactPath
AutoGenerate 0;

tabDates:
LOAD Distinct
    Date as DateSort
Resident tabTransact
Order By Date;

FOR Each vDate in FieldValueList('DateSort')
Join (tabTransactPathTemp1)
LOAD TransactID,
    From as Account,
    To
Resident tabTransact
Where Date='$(vDate)';

tabTransactPathTemp2:
LOAD Text(TransactPath&If(Len(TransactPath) and Len(TransactID),'/')&TransactID) as TransactPath,
    To as Account
Resident tabTransactPathTemp1;

DROP Table tabTransactPathTemp1;

RENAME Table tabTransactPathTemp2 to tabTransactPathTemp1;
NEXT vDate;

tabTransactPath:
LOAD *
Where Len(TransactID);
LOAD TransactPath,
    SubStringCount(TransactPath,'/')+1 as TransactPathLen,
    SubField(TransactPath,'/') as TransactID,
    SubField(TransactPath,'/',1) as StartTransactID;
LOAD Text(Mid(TransactPath,Index('/'&TransactPath,'/',IterNo()))) as TransactPath
Resident tabTransactPathTemp1
While IterNo()<=SubStringCount(TransactPath,'/')+1;

Left Join (tabTransactPath)
LOAD TransactID as StartTransactID,
    From as Account
Resident tabTransact;

DROP Tables tabDates, tabTransactPathTemp1;

hope this helps

regards

Marco