2 sources: Common fields

[#1]

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.

[vinieme12]

There is no relationship between EOD and Marketplace table ?

[#3]

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

[vinieme12]

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

[rubenmarin]

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.

[#6]

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.

[#7]

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.

[vinieme12]

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

[#9]

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.

[rubenmarin]

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.