topic Data excluded from Qvd in QlikView

Data excluded from Qvd

Fri, 14 Aug 2015 11:42:17 GMT

I'm trying to bring in data from 3 different tables. I've loaded the tables indiviually and sent the output to save as QVD and then I have dropped the tables. I've then bought in the 3 QVD files and joined them using a left join. It seems I'm getting the data from the first table and not the other 2.

I've attached the QVD

Re: Data excluded from Qvd

Fri, 14 Aug 2015 12:08:40 GMT

Please make sure that your join field values are same means case in sensitive.

Re: Data excluded from Qvd

m_woolf — Fri, 14 Aug 2015 12:09:35 GMT

Should you be concatenating the QVDs instead of left joining?

Re: Data excluded from Qvd

jonathandienst — Fri, 14 Aug 2015 12:09:53 GMT

A LEFT JOIN is a filtering join - it will only bring in the records where all the key fields match. For the second table, the key fields are

AFKO_WBS,

PRPS_WBS,

WBS,

CostElement,

TotalCosts,

MaterialCosts,

LabourCosts,

LabourHours,

CostGroup,

ValueType,

PlanVersion

Only the following are NOT key fields

SalesActualCostsLC,

SalesActualCostsGC,

TradingPartner,

The second join is similar.

Perhaps you should be concatenating the tables. Or possibly an Outer Join.

Re: Data excluded from Qvd

Fri, 14 Aug 2015 12:15:00 GMT

According to your script, your have to concatenate these 3 tables not join because looks like all 3 tables have the same set of data granularity

In Qlikview JOIN is joining tables like SQL. Concatenate is SQL UNION type function.

So concatenate table with Concatenate function..

Costing_Table:

LOAD AFKO_WBS,

PRPS_WBS,

If(AFKO_WBS <> 'NA', AFKO_WBS, PRPS_WBS) As WBS,

CostElement,

TotalCosts,

MaterialCosts,

LabourCosts,

LabourHours,

CostGroup,

PlannedCostsLCV0,

PlannedCostsLCV1,

PlannedCostsGCV0,

PlannedCostsGCV1,

ActualCostsLC,

ActualCostsGC,

ValueType,

PlanVersion

FROM (qvd);

Concatenate(Costing_Table)

LOAD AFKO_WBS,

PRPS_WBS,

If(AFKO_WBS <> 'NA', AFKO_WBS, PRPS_WBS) As WBS,

CostElement,

TotalCosts,

MaterialCosts,

LabourCosts,

LabourHours,

CostGroup,

PlannedCostsLCV0,

PlannedCostsLCV1,

PlannedCostsGCV0,

PlannedCostsGCV1,

PlannedHoursV0,

PlannedHoursV1,

ActualHours,

ActualCostsLC,

ActualCostsGC,

ValueType,

PlanVersion

FROM (qvd);

Concatenate(Costing_Table)

LOAD AFKO_WBS,

PRPS_WBS,

If(AFKO_WBS <> 'NA', AFKO_WBS, PRPS_WBS) As WBS,

CostElement,

TotalCosts,

MaterialCosts,

LabourCosts,

LabourHours,

CostGroup,

SalesActualCostsLC,

SalesActualCostsGC,

TradingPartner,

ValueType,

PlanVersion

FROM (qvd);