topic Re: How To Subtract One Table From Another To Get Third Table in QlikView

How To Subtract One Table From Another To Get Third Table

pnn44794 — Sat, 12 Oct 2019 20:53:04 GMT

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: How To Subtract One Table From Another To Get Third Table

pnn44794 — Mon, 07 May 2018 19:41:11 GMT

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: How To Subtract One Table From Another To Get Third Table

pnn44794 — Sat, 12 Oct 2019 20:51:26 GMT

My latest attempt has been the following expression.

Re: How To Subtract One Table From Another To Get Third Table

pnn44794 — Sat, 12 Oct 2019 20:15:09 GMT

Ignore

Re: How To Subtract One Table From Another To Get Third Table

pnn44794 — Sat, 12 Oct 2019 20:52:15 GMT

I've resolved the issue by doing the following:

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)

Problem solved.

Re: How To Subtract One Table From Another To Get Third Table

pnn44794 — Mon, 21 May 2018 17:08:54 GMT

Test.

Re: How To Subtract One Table From Another To Get Third Table

Kai_DesEtages — Mon, 21 May 2018 19:23:38 GMT

test