Data modeling

Fri, 08 Jul 2016 16:00:45 GMT

I want to show differences between 2 tables.

First table holds the following:

Shop total articles scanned

Shop_RetourNr, shopnr articlenr and number_scanned_shop

Articles are shipped to distribution center (DC)

Shop total articles scanned

DC_RetourNr, shopnr articlenr and number_scanned_DC

to make the row unique in DC table there is an extra field called receipts number.

I use the following key

RetourNr & '.' & ShopNr & '.' & ArticleNr as key,

all fields present in both tables. In shop table all the rows are unique. For DC keys there can be duplicates.

What I want to do is check if there are "total scanned differences" using RetourNr as aggregation level.

I do this:

mapRetourNr_shop:

mapping load Shop_RetourNr, sum(number_scanned_shop) as Shop_retournr_total_scanned

resident incidenteel_temp

group by Shop_RetourNr;

For DC totals scanned I do the same.

If I make a Straight table as dimension the key showing Shop_retournr_total_scanned versus DC_retournr_total_scanned I got a lot of differences.

When I export the data to Excel some magic is going on the data fits for 99%.

Thanks for helping me a great weekend!

John

Re: Data modeling

boorgura — Fri, 08 Jul 2016 17:07:47 GMT

Am not sure if I understand the requirement completely.

If you are looking to populate the scans which are done only at one place. (and not at the other)

Shop:

LOAD Shop_RetourNr,

...

from shop_source;

DC:

LOAD DC_RetourNr,

...

from DC_Source;

Final:

LOAD Shop_RetourNr as RetourNr

resident Shop

where not exists(DC_RetourNr, Shop_RetourNr)

concatenate(Final)

LOAD DC_RetourNr as RetourNr

resident DC

where not exists(Shop_RetourNr, DC_RetourNr);

drop tables DC, Shop;

I just have one field in the Final table, you can all of them needed.