Comparing two data sets and Get dimension from latest data set while joining
I have product sales data sets with history and Daily.For daily data the product category for a product may different compared to month end history data set for the same product.I want to join two data sets based on some filters and output table must have the product category as of Daily data but not month end data.In SQL we can use COALESCE function.I am working on QVD's.How to write script for Joining two different data sets (History and Daily) with same fileds but final table must have product category (dimension) as of Daily data irrespective of history or daily data.?
Have you tried concatenating the two tables but when loading in the history table don't load in the product category if all the product categories you need are in the daily table. This is assuming that all of the fields are the same in both tables and that you would never need the data in the product category field from the history table...
Then store the concatenated table and drop it from memory, then from where you stored it load the fields with a Load Distinct. You can also add your where clause in this load as well.