topic Re: 2 sources: Common fields in QlikView

2 sources: Common fields

Tue, 06 Dec 2016 10:46:50 GMT

Hi,

I have two files from two different sources. One file is from our internal database and one is clients.

My goal with this application is to notice differences.

I have attached my application and some pictures.

Based on some fields i'd like them to only show if theres any difference.

settledate + price + issell on one side contra

eod_SD + eod_price + eod_Side on the other side. They should be exact the same if not then show the diff.

Re: 2 sources: Common fields

vinieme12 — Tue, 06 Dec 2016 11:02:00 GMT

There is no relationship between EOD and Marketplace table ?

Re: 2 sources: Common fields

Tue, 06 Dec 2016 11:04:21 GMT

No they are from different sources. Different departments actually. Abasec is my side and EOD is their side.

I have not made any joins together or anything

Re: 2 sources: Common fields

vinieme12 — Tue, 06 Dec 2016 11:08:36 GMT

if there is nothing to tie back the rows of data from each table, how do we check??

Re: 2 sources: Common fields

rubenmarin — Tue, 06 Dec 2016 11:11:21 GMT

Hi Niklas, you can LOAD a table with the composed key as a map and apply this map to the other table to flag differences:

MAP_Table2:

Mapping LOAD settledate &'_'& price &'_'& issell as key, //'_' is only a aseparator

0 as Different //If not found here the record is different

Resident/FROM/SELECT Table2;

Table1:

LOAD *, applymap('MAP_Table2', eod_SD &'_'& eod_price &'_'& eod_Side, 1) as Different

Resident/FROM/SELECT table 1;

You can also add the map from table 1 and load table 2 to flag recods on both tables.

Re: 2 sources: Common fields

Tue, 06 Dec 2016 11:18:39 GMT

Well I havent made a join or anything but they have common fields where the should match and that is:

settledate + price + issell on one side contra

eod_SD + eod_price + eod_Side on the other side.

If they don't thats the difference I want to be shown.

Re: 2 sources: Common fields

Tue, 06 Dec 2016 11:21:12 GMT

Could you possibly make this syntax in the app? Im not familiar with applymaps. But I understand your logic to make a key on that specific fields and compare them.

Re: 2 sources: Common fields

vinieme12 — Tue, 06 Dec 2016 11:24:47 GMT

if there are multiple orders on the same dates from the same side , then even a composite key won't work

Re: 2 sources: Common fields

Tue, 06 Dec 2016 11:31:49 GMT

I'm not 100% I follow you here sorry.

Based on the fields I mentioned and maybe also lsearchname there wont be any more? That make a unique row.

Re: 2 sources: Common fields

rubenmarin — Tue, 06 Dec 2016 12:05:14 GMT

Sorry Niklas, I don't have an available license so I can't open the app.

To explain the code:

- First creates the keys (one string with all the fields) to search in a map (A load starting with 'Mapping'). The map has 2 fields: the value to search and the value that returns when is found (it this case all returns zero)

MAP_Table2:

Mapping LOAD settledate &'_'& price &'_'& issell as key, //'_' is only a aseparator

0 as Different //If found here the record is not different

Resident/FROM/SELECT Table2;

- Next step is Load the other table, applymap looks if the key created with the fields of this table matches any records of the previous table, if a similar record is found it retuns zero (the value of the map). If no similar record was found it returns 1. So the different records will be marked with this field.

Table1:

LOAD *, applymap('MAP_Table2', eod_SD &'_'& eod_price &'_'& eod_Side, 1) as Different

Resident/FROM/SELECT table 1;

The syntaxis for ApplyMap is: ApplyMap(Map_To_Look, Value_To_Search, [Value_To_Retun_If_Not_Found]) // If the value is found it retuns the 2nd field of the map.

Re: 2 sources: Common fields

Tue, 06 Dec 2016 12:47:38 GMT

My key i generated by fields from 3 tables

contnotes gives me settledate,

instruments gives me lsearchname,

marketplacedeals gives me issell, price

Does that affect your mappingload?

Re: 2 sources: Common fields

rubenmarin — Tue, 06 Dec 2016 13:44:22 GMT

It will be based on the checks you want to do, from what I undesrtand of your first post, to check 3 fields at a time means those 3 tables are related, and you want to check each related row, so to do this the tables should be joined first in one table, from this table you can create the key.