Linking tables correctly to bring fields together in a chart

mgoddard — Wed, 11 Oct 2023 12:12:43 GMT

I have a pivot table (below) and need to display by FC_Month for each Raw_Item_Key value the Demand (calculated from 'SalesQTY') field in All_SIOP_Data - which is working AND another expression called 'Due to Del' which is the sum of 'Open Qty' in the OpenPurOrders table. FY_FC_Month (OpenPurOrders table) and FC_Month (All_SIOP_Data table) are the same but I had to rename to avoid circular linkage.

Here is the pivot and table layout.

I am newer to Qlik scripting and would like to know the solution to be able to sum(Open Qty) in my pivot table, by either making a load script change or the correct expression in the chart. I assume that somehow I need to write that FY_FC_Month=FC_Month somewhere?

Thank you in advance.

Re: Linking tables correctly to bring fields together in a chart

Mark_Little — Wed, 11 Oct 2023 12:19:47 GMT

Hi,

Scripting wise have a couple of options, you would either need to concatenate the two table to create a single Fact table, Where the fields contain the same data ensure they have the same name and create a field to label wish lines are forecast and which are Open orders. This can then we used in your front end set analysis to ensure you get the correct values.

You need to create a link table that that sits in between the to Fact tables and then can link off to any dimensions.

I normally approach this kind of problem with the first method.

topic Re: Linking tables correctly to bring fields together in a chart in App Development

Linking tables correctly to bring fields together in a chart

Re: Linking tables correctly to bring fields together in a chart