Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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;
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
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;
where are these following fields in tables?
custTotalValue
EstCompletionDate
EstStartDate
EstCompletionDate
Fields go to these tables; I accidently removed them to shorten the script.
custTotalValue = Cust_Opportunities
EstCompletionDate = Opportunities
EstStartDate = Opportunities
EstCompletionDate = Opportunities
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;
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;
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;
Thank you both Mayil and Prem, amazing how many ways it can be done. I guess I was having trouble on the syntax.
Brian