How To Subtract One Table From Another To Get Third Table
Hello:
I have three Qlikview pivot tables that I'll call A, B and C. Please see the attached Word document for calculated dimensions and screenshots of the tables. I need to subtract like Product amounts (2018-Qx columns) in Table B from Table A and populate Table C with the results. I'm not sure where to start in order to do the calculation. It seems simple in concept, but code-wise perhaps not, but I could be wrong. Table A Product Amount - Table B Product Amount = Table C Product Amount.
In this case, with the screenshots you'll see, Table C should have 0 amounts for both quarters and for all products. This is a good thing because that means I had no variance in what was forecasted (Table A) and what was received and bucketed (Table B). I also have a problem getting the Product column for Table C in the same order as Tables' A and B, but the bigger problem is the calculation.
The "Order Quarter -->" is a label given to order_date_quarter for Table A and LF_effective_quarter for Table's B and C. There's one expression used for Table's A and B (not sure what's needed for Table C obviously) and they are included in the attached Word document. I've also attached sample data, appropriately scrambled, in case it's needed. The data used for Table's A and B are joined by Qlikview using project_id.
As always, any and all help and responses are appreciated. Thanks in advance.
Re-did the data model by de-coupling the Forecast (has all forecasts) and Latest Forecast fact tables - They should have been independent to begin with
Created a third fact table with just the bulk forecast being put forward
Created a link table to link the Latest Forecast with the newly created fact table (Note that this creates a loosely coupled data model, but that's probably ok in this case as they shouldn't be solidly linked anyway - Still, I need to look at fixing this too)
I've solved the sort order problem by creating a Product Group table and then loading it in load order. I could still use some help though with the calculation, if anyone has any ideas.
As an FYI, there's one project_id that ties Table A to Table B and that is 14243. However, if needed, it would probably be best to use DM # from Table A and LF_project_ext_project_no from Table B. The value would be 88888888*.
Re-did the data model by de-coupling the Forecast (has all forecasts) and Latest Forecast fact tables - They should have been independent to begin with
Created a third fact table with just the bulk forecast being put forward
Created a link table to link the Latest Forecast with the newly created fact table (Note that this creates a loosely coupled data model, but that's probably ok in this case as they shouldn't be solidly linked anyway - Still, I need to look at fixing this too)
