Resident Load after Inner Join...need help

[b_garside]

Hi I'm trying to do some ETL on some fields from previously two different tables. Both which are inner joined. That part works.

The trouble I'm having is with adding a preceding load on top of the new Fact table that was just inner joined. How can I add this pice to the main Opportunities fact table. What did I do wrong?  adding field [dol_Contract_Value_Year1]

Load

custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1, Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]

[Opportunities]:

LOAD OpportunityID

     State,
Status,
Supervisor,
Timescale,
WeightedRevenue,
Zip
FROM
$(vPathDirectory)Opportunity.qvd (qvd);

Inner Join (Opportunities) //Joins on [OpportunityID]

[Cust_Opportunities]:
LOAD OpportunityID

     CustProposalCoordinator,
CustPastPerformanceLead,
CustProgramManager,
CustStaffingPlanLead,
CustBookBoss,
CustWriter1,
CustWriter2,

     CustBidType
FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
(qvd);

[MayilVahanan]

Hi

I didn't see any field like that..

Anyway, Try like this

After joining two tables, use resident table for your calculation.

Qlikview:
First load from table, then calculate preceding load, after tat only join the another table.

hope tat helps

[b_garside]

tried this but it just doubled my rows.

[Opportunities]:

LOAD OpportunityID

   EstCompletionDate,

   EstStartDate,

  EstCompletionDate,

     State,
Status,
Supervisor,
Timescale,
WeightedRevenue,
Zip
FROM
$(vPathDirectory)Opportunity.qvd (qvd);

Inner Join (Opportunities) //Joins on [OpportunityID]

[Cust_Opportunities]:
LOAD OpportunityID

custTotalValue,     

CustProposalCoordinator,
CustPastPerformanceLead,
CustProgramManager,
CustStaffingPlanLead,
CustBookBoss,
CustWriter1,
CustWriter2,

     CustBidType
FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
(qvd);

Concatenate Load

custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1, Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]

Resident Opportunities;

[preminqlik]

where are these following fields in tables?

custTotalValue

EstCompletionDate

EstStartDate

EstCompletionDate

[b_garside]

Fields go to these tables; I accidently removed them to shorten the script.

custTotalValue = Cust_Opportunities

EstCompletionDate = Opportunities

EstStartDate = Opportunities

EstCompletionDate = Opportunities

[preminqlik]

tab2:

Inner Join (Opportunities)

LOAD OpportunityID

custTotalValue,    

CustProposalCoordinator,
CustPastPerformanceLead,
CustProgramManager,
CustStaffingPlanLead,
CustBookBoss,
CustWriter1,
CustWriter2,

     CustBidType
FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
(qvd);

tab3:

noconcatenate

load *,

custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1,Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]

resident tab2;

drop table tab2;

[MayilVahanan · Accepted solution]

Hi

[Opportunities]:

LOAD OpportunityID

   EstCompletionDate,

   EstStartDate,

  EstCompletionDate,

     State,
Status,
Supervisor,
Timescale,
WeightedRevenue,
Zip
FROM
$(vPathDirectory)Opportunity.qvd (qvd);

Inner Join (Opportunities) //Joins on [OpportunityID]

[Cust_Opportunities]:
LOAD OpportunityID

custTotalValue,    

CustProposalCoordinator,
CustPastPerformanceLead,
CustProgramManager,
CustStaffingPlanLead,
CustBookBoss,
CustWriter1,
CustWriter2,

     CustBidType
FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
(qvd);

FinalOpportunities:

Load

*,
custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1, Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]
Resident Opportunities;

Drop table Opportunities;

[b_garside]

ok, that looks good. I ended trying two inner joins and it worked. 

[Opportunities]:

LOAD OpportunityID

   EstCompletionDate,

   EstStartDate,

  EstCompletionDate,

     State,
Status,
Supervisor,
Timescale,
WeightedRevenue,
Zip
FROM
$(vPathDirectory)Opportunity.qvd (qvd);

Inner Join (Opportunities) //Joins on [OpportunityID]

[Cust_Opportunities]:
LOAD OpportunityID

custTotalValue,    

CustProposalCoordinator,
CustPastPerformanceLead,
CustProgramManager,
CustStaffingPlanLead,
CustBookBoss,
CustWriter1,
CustWriter2,

     CustBidType
FROM $(vPathDirectory)OpportunityCustomTabFields.qvd
(qvd);

Inner Join (Opportunities) //Joins on [OpportunityID] resulting in one Fact table

[dol_Contract_Value_Year1] :
Load OpportunityID,
custTotalValue / If( (EstCompletionDate-EstStartDate) <=365,1, Year(EstCompletionDate)-Year(EstStartDate) )   as [dol_Contract_Value_Year1]
Resident Opportunities;

[b_garside]

Thank you both Mayil and Prem, amazing how many ways it can be done. I guess I was having trouble on the syntax.

Brian