Mapping Data

[bimanbeginner]

Hi There

I have a data set with two criteria.

1.is system data showing payments from different clients

2.is statement data showing payments from clients

I need to map the data  so that in one line I can find the statement and system data

eg:

System Payment from Client John for 2000 the detail on  the statement is Johnpmnt

with the attached data can I use any function within qlikview to achieve this? Apply Map etc.?

Regards,

[sunny_talwar]

Would you be able to show what the expected output is?

[bimanbeginner]

  Hi Sunny

This is basically what I need to see, I know without a unique key to link the two sets this seems quite impossible...Im not sure how else to resolve this though.

Amount	Name	DocNum	Detail
2000	John	212	JohnPmnt
2000	Peter	212	PeterPayment
2000	Paul	212	PayMentFrmPaul
2000	Ivan	212	Van02
1000	Reggie	256	Payment1

[MindaugasBacius]

How are you willing to connect the data?

As I understand there are two tables System and Statement.

Both of the tables should be connected by the fields Amount & DocNum & wildmatch between Detail and Name, am I correct?

[bimanbeginner]

Yes you are correct, The data is recorded in one table split by the account

[bimanbeginner]

In My actual table there is over 100k records.

Is there maybe a way I could create a mapping table based on the previous records to identify which detail belongs to which name based on common references(detail) used before in the previous records...

[MindaugasBacius]

I am struggling to feature out how have you connected:

In field Detail there are Names that can be linked, but for Ivan and Reggie there are only Amount and DocNum according to what you can connect the data:

[qliksus]

Based on the sample you provided I have done this code But you may have to test it into your real data

a:
LOAD * INLINE [
    Account,Amount,Detail,DocNum,Dr_Cr,Name
    Statement, 1000, Payment1, 256, Credit,
    Statement, 2000, JohnPmnt, 212, Credit,
    Statement, 2000, PayMentFrmPaul, 212, Credit,
    Statement, 2000, PeterPayment, 212, Credit,
    Statement, 2000, Van02, 212, Credit,
    System, 1000, , 256, Debit, Reggie
    System, 2000, , 212, Debit, Ivan
    System, 2000, , 212, Debit, John
    System, 2000, , 212, Debit, Paul
    System, 2000, , 212, Debit, Peter
];

/******* This is to Make the full outer join to duplicate Each Detail with the corresponding Name ***********/

A1:
load Detail as Detai1l,Detail as Detail2
Resident a ;
Join
load Name as Name1,Name as Name2
Resident a ;

/***************************************************************************************************************/

/******* This is to compare the Detail and Name based on the common Match i.e Comparing the value John with JohnPmnt  likewise the matching string     *********************************************************/

load  *
Where Key<>'1';
load Detai1l as DetailNew,Name1 as Name ,
if( SubStringCount(Detai1l,Name1)  or WildMatch(Name1,'*'&purgechar(Detai1l,'0123456789')&'*')  ,  Name1,1) as Key
Resident A1
Where Detai1l<>'' ;

/************************************************************************************************/

//****************** This to take the not matching string and assuming this as same i.e the Reggie to Payment1 scenario  *********************//

load * ,'' as Key
Where (len(DetailNew)>1 and len(Name)>1  )  ;

load if(  not Exists(DetailNew,Detai1l),Detai1l) as DetailNew,if(  not Exists(Key,Name1),Name1)  as Name
Resident A1

;

//*************************************************************************************//

DROP Table A1 ;

If there are more records like the Reggie then there would be issues

[bimanbeginner]

Thank You Qliksus , I am in the process of testing this.