topic Re: Preceding Load on Preceding Load...need help in QlikView

Resident Load after Inner Join...need help

b_garside — Tue, 22 Oct 2013 18:21:08 GMT

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

Re: Nested preceding load...need help

MayilVahanan — Tue, 22 Oct 2013 18:34:42 GMT

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

Re: Preceding Load on Preceding Load...need help

b_garside — Tue, 22 Oct 2013 18:48:45 GMT

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;

Re: Resident Load after Inner Join...need help

preminqlik — Tue, 22 Oct 2013 18:58:11 GMT

where are these following fields in tables?

custTotalValue

EstCompletionDate

EstStartDate

EstCompletionDate

Re: Preceding Load on Preceding Load...need help

b_garside — Tue, 22 Oct 2013 19:03:02 GMT

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

custTotalValue = Cust_Opportunities

EstCompletionDate = Opportunities

EstStartDate = Opportunities

EstCompletionDate = Opportunities

Re: Resident Load after Inner Join...need help

preminqlik — Tue, 22 Oct 2013 19:15:07 GMT

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;

Re: Preceding Load on Preceding Load...need help

MayilVahanan — Tue, 22 Oct 2013 19:17:55 GMT

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

Re: Preceding Load on Preceding Load...need help

b_garside — Tue, 22 Oct 2013 19:20:49 GMT

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;

Re: Preceding Load on Preceding Load...need help

b_garside — Tue, 22 Oct 2013 19:38:19 GMT

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

Brian