Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Data excluded from Qvd


Hi

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

4 Replies
Not applicable

Re: Data excluded from Qvd

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

mwoolf
Honored Contributor II

Re: Data excluded from Qvd

Should you be concatenating the QVDs instead of left joining?

MVP
MVP

Re: Data excluded from Qvd

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Data excluded from Qvd

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);

Community Browser