Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

b_garside
Valued Contributor

Resident Load after Inner Join...need help

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



1 Solution

Accepted Solutions

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

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;


8 Replies

Re: Nested preceding load...need help

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
Valued Contributor

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

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
Valued Contributor II

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

where are these following fields in tables?

custTotalValue

EstCompletionDate

EstStartDate

EstCompletionDate

b_garside
Valued Contributor

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

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

custTotalValue = Cust_Opportunities


EstCompletionDate = Opportunities

EstStartDate = Opportunities

EstCompletionDate = Opportunities


preminqlik
Valued Contributor II

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

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

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
Valued Contributor

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

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
Valued Contributor

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

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

Brian

Community Browser