Combine 3 relational files to one flat files and add some measures in order to make reporting faster
The tables are in advantage data files from Sybase (server and Local server)
[login to view URL]
This job deals with 3 files:
TmpCalc –Sales Tickets details
Prdmstr –Product Master
MixHD – Mix and Match
And a new file [login to view URL] - Daly Sales
The job is to transform the files into a flat file each row to have the sum sales of a given product in a day and other attributes from the product file and some measures
The measures includes:
highest price sold
Quan sold @ highest price
Lowest price sold
Quan Lowest price
Count sold with other prices (maybe there is another way of giving this valuable info like the price mostly used, or the 3 main prices and their count and then other prices and their count)
Also date dimensions would be added like;
Day of week
Week of year
If Cost is different in 2 records for same item for same day in Tmpcalc then a separate record should be created.
Job would run once an hour as a scheduled task and only update the records that was not updated yet.
We are adding a field LsUpdated in Tmpcalc that will contain DateTime of last change made. ETL should keep a DateTime of last reocrd imported [max(LsUpdated)] and import all records with a later LsUpdated value.
The sales person could go back and edit a old ticket this is why if a ticket is edited after it is already in the [login to view URL] the next time it is being updated all of the tickets with this item, date and cost has to be recalculated
If the tmpcalc->code is empty or not found in [login to view URL] then use tmpcalc->Desc
In order to be granted this job we would need a demo (could be compiled in some way or time limited) and we would test it on our data you could use some SQL script or a ETL program, currently we use pentaho for ETL jobes and Delphi for our apps.
i would send you a link for the data files and its layout