topic Re: Mapping Data in QlikView

Mapping Data

bimanbeginner — Wed, 22 Jun 2016 15:39:48 GMT

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,

Re: Mapping Data

sunny_talwar — Thu, 23 Jun 2016 03:39:05 GMT

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

Re: Mapping Data

bimanbeginner — Thu, 23 Jun 2016 04:46:55 GMT

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

Re: Mapping Data

MindaugasBacius — Thu, 23 Jun 2016 05:40:26 GMT

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?

Re: Mapping Data

bimanbeginner — Thu, 23 Jun 2016 05:56:05 GMT

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

Re: Mapping Data

bimanbeginner — Thu, 23 Jun 2016 06:08:41 GMT

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...

Re: Mapping Data

MindaugasBacius — Thu, 23 Jun 2016 09:18:40 GMT

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:

Re: Mapping Data

qliksus — Fri, 24 Jun 2016 05:49:12 GMT

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

Re: Mapping Data

bimanbeginner — Fri, 24 Jun 2016 11:57:14 GMT

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