show excluded rows in load

Wed, 13 Jul 2016 15:10:40 GMT

Hello, for the purpose of my question i have made some tables with static data.

I am starting from the Sales table without any problem, next i am linking Budget to Sales via BudgetKey.

But the problem i am facing is that the VPA2 record from Budget is not shown in my report. So i thought to solve this via OUTER JOIN but for some strange reason this just doesn't work... the VPA2 line is still not shown in my report.

I do this (in combination with a Date Island) via

=sum(if(Budget.boekjaar = DI_Financial_Year and Budget.boekjaar_periode = DI_Financial_Month_Num, aggr(Budget.lmvh_budget, BudgetKey)))

Any help? Thanks!

Sales:

LOAD * inline [

VtgwBedrKey, BudgetKey, lmvh, lvol, Article, Client, Client_Type, Company, Country_Of_Destination, Customer_Group, Financial_date, Warehouse, lprmargomz, dag, maand, jaar, Article_Type, Region, orbedrijf, Invoice_Number, Article_Type_Description

'PPD|999', 'Agroup|FY2017|1|999|PPD|PELL', etc...........................

'PPD|999', 'Agroup|FY2017|1|999|PPD|PELL', etc...........................

'PPD|999', 'Agroup|FY2017|1|999|PPD|PELL', etc...........................

'PPD|999', 'Agroup|FY2017|1|999|PPD|VPA1', etc...........................

'PPD|999', 'Agroup|FY2017|1|999|PPD|VPA3', etc...........................

];

QUALIFY *;

UNQUALIFY BudgetKey;

Budget:

OUTER JOIN(Sales) //this line was added extra because VPA2 was not shown with normal linking

LOAD * inline [

BudgetKey, klantengroep, boekjaar, boekjaar_periode, vtgwcode, bedrijf, artsoort, lmvh_budget

'Agroup|FY2017|1|999|PPD|PELL', 'Agroup', 'FY2017', 1, 999, 'PPD', 'PELL', 1

'Agroup|FY2017|1|999|PPD|VPA1', 'Agroup', 'FY2017', 1, 999, 'PPD', 'VPA1', 1

'Agroup|FY2017|1|999|PPD|VPA2', 'Agroup', 'FY2017', 1, 999, 'PPD', 'VPA2', 1

];

UNQUALIFY *;

Re: show excluded rows in load

Gysbert_Wassenaar — Thu, 14 Jul 2016 11:52:46 GMT

Can you post a small qlikview document that demonstrates the problem? Fake data is fine as long as it makes the problem clear.

Re: show excluded rows in load

jonathandienst — Thu, 14 Jul 2016 12:02:19 GMT

The outer join probably loads the record, but the VPA2 record is not associated with any record in the first table. If you use any of the first table fields as dimensions in a chart or table, the VPA2 record will not show up, as its not associated to any of the dimension values.

You can verify this by creating a list box for lmvh_budget.

Re: show excluded rows in load

Thu, 14 Jul 2016 13:08:40 GMT

Hello,

Thanks for the answers but i eventually solved it at SQL level.

topic show excluded rows in load in QlikView

show excluded rows in load

Re: show excluded rows in load

Re: show excluded rows in load

Re: show excluded rows in load