topic Re: Linking ID's in QlikView

Linking ID's

Tue, 30 Jun 2015 11:48:18 GMT

Hi,

Just hoping somebody can help me with a query of what I am trying to do and if they can even tell me if it is even possible to do with Qlikview.

I have attached an Excel file below which is a sample file of the kind of data I am working on.

My problem is I have two different Files one which contains all the names of every Company signed up and their ID number. Those are the two last columns called Name and IDS.

The first 7 columns is from a sheet with the ID number of all the company's that have signed up and the details of their sign up.

As you can see from the file some of the IDs on the last column are not on the IDs on the first column because some companies didn't pay this sign up fee. Also while most IDs on both columns are the same they are not in line which is also a problem.

I am wondering if their is anyway I can do something to be able to count and show the accounts that did not pay the sign up fee. I presume I need some sort of join but I have no idea how to do that.

Thanks.

Re: Linking ID's

hic — Tue, 30 Jun 2015 12:10:49 GMT

I would do the following:

File1:
LOAD ID,
ID as ID1,
Situation,
Desc,
Amount,
Date,
Cost,
Ammount_Recieved
FROM [Sample Community.xlsx] (ooxml, embedded labels, table is Sheet1);

File2:
LOAD Name,
IDS as ID2,
If(Exists(ID1,IDS),'Yes','No') as HavePaid,
IDS as ID
FROM [Sample Community.xlsx] (ooxml, embedded labels, table is Sheet1);

Then you can do your analysis using "HavePaid" or by putting multiple fields in a table.

HIC

Re: Linking ID's

maxgro — Tue, 30 Jun 2015 12:14:33 GMT

PFA