topic Re: Nested Preceding load ? in QlikView

Nested Preceding load ?

b_garside — Thu, 20 Jun 2013 17:54:01 GMT

Sample script for my Preceding load. Im tryin to do some Apply mapping on just these fields (not finished) while the rest in the SQL Select I just need to load as is. Right now it only loads the ones in the precdieng load even though I have the LOAD *; at the end to pull the rest in?

What Im a doign wrong do I still need to use Resident ?

[OpportunityCustomTabFields]:

LOAD

ApplyMap('MAP_EmployeeToFullName',CustProposalCoordinator, Null() ) AS CustProposalCoordinator,
CustPastPerformanceLead,
ApplyMap('MAP_EmployeeToFullName',CustProgramManager, Null() ) AS CustProgramManager,
CustStaffingPlanLead,
CustBookBoss,
CustWriter1,
CustWriter2,
CustWriter3,
CustWriter4;

LOAD *;

SQL SELECT custApprovedBy,
custAwardDate,
custAwardDate3B,
CustBackupBusinessSector,
CustBackupCOE,
CustBackupServiceSector,
custBAFODate,
custBidDecision,
custBiddersConference,
custBlackTeamReviewDate,
custBlueTeamReviewDate,
custBPNumber,
custCaptureStrategy,
CustCDBSubmit,
CASE WHEN CustClassified1 Is Null THEN 'Not Set' ELSE CustClassified1 END AS CustClassified1,
custClientProgramManager,
CASE WHEN custCOE Is Null THEN '-' ELSE custCOE END AS custCOE,
.....

Re: Nested Preceding load ?

Gysbert_Wassenaar — Thu, 20 Jun 2013 18:06:21 GMT

Your top load statement doesn't load all fields, but only those you explicitly listed. The second load *; lines is not necessary. It doesn't add do anything useful. It only passes the results from the sql load to the top load. But you can leave it out. It's not necessary. See this blog post.

What you do need to do is list all the fields you want in the final table in the top load statement.

Re: Nested Preceding load ?

b_garside — Thu, 20 Jun 2013 18:15:40 GMT

Ok, yep. Was hoping to not to list all the fields explicitly. But makes sense if there is no alternative.

Thanks for the quick reply!

Re: Nested Preceding load ?

whiteline — Thu, 20 Jun 2013 20:26:47 GMT

You could use both asterisk and explicetly defined feilds:

LOAD

feld1,

field2;

SELECT ...;

Of couse, there should be no duplicates.

Re: Nested Preceding load ?

b_garside — Thu, 20 Jun 2013 20:29:49 GMT

Nice, I will have to give it a try. I guess I had the LOAD * , in the wrong place at bottom instead of top.

Re: Nested Preceding load ?

b_garside — Thu, 20 Jun 2013 21:05:15 GMT

Duplicates in the preceding or SQL Select? still getting an error....OLEDB Read error

[OpportunityCustomTabFields]:

LOAD //Preceding Load used for ETL, pulls from SQL Select below

* , //Load everything, but Do this ApplyMap first

ApplyMap('MAP_EmployeeToFullName',CustProposalCoordinator, Null() ) AS CustProposalCoordinator,

ApplyMap('MAP_EmployeeToFullName',CustPastPerformanceLead, Null() ) AS CustPastPerformanceLead,

ApplyMap('MAP_EmployeeToFullName',CustProgramManager, Null() ) AS CustProgramManager,

date(floor(custProposalDueDate)) as custProposalDueDate;

SQL SELECT custApprovedBy,

custAwardDate,

custAwardDate3B,

CustBackupBusinessSector,

CustBackupCOE,

CustBackupServiceSector,

custBAFODate,

custBidDecision,

custBiddersConference,

custBlackTeamReviewDate,

Re: Nested Preceding load ?

whiteline — Fri, 21 Jun 2013 07:02:57 GMT

In the preceding.

Imagine than QV replaces asterisk with the list of all fields. Then you add:

ApplyMap('MAP_EmployeeToFullName',CustProposalCoordinator, Null() ) AS CustProposalCoordinator,

But there is a field with similar name already.

You have either to:

1) Use asterisk and different names for mapped fields, then drop unnecessary fields.

2) List and name all fields explicitly.

Re: Nested Preceding load ?

er_mohit — Fri, 21 Jun 2013 07:37:50 GMT

try simply like this

TB_M_LEAVEPLANT_TYPE_MASTER:

LOAD LeaveTypeID,

LeaveType,

NoOfDaysEntitled,

DaysAccumulatable,

EncashmentRule,

HalfDayPermissible,

BaseLimitApplied,

LeaveTypeSName,

LeaveGrpID,

MinDeductedDays,

SaviorLeaveCode;

SQL SELECT *

FROM "IGL_APS".dbo."TB_M_LEAVEPLANT_TYPE_MASTER";

[OpportunityCustomTabFields]:
//Here you load all fields which is working as precedence load while fetchind data from table it gives you an option at last right corner for select precedence load when you tick on automatically yuor all fields come from your SQL Database as above i mentioned the example
LOAD

custApprovedBy,

custAwardDate,

custAwardDate3B,

CustBackupBusinessSector,

CustBackupCOE,

CustBackupServiceSector,

custBAFODate,

custBidDecision,

custBiddersConference,

custBlackTeamReviewDate,

custBlueTeamReviewDate,

custBPNumber,

custCaptureStrategy,

CustCDBSubmit

if(isnull(CustClassified1),'Not Set',CustClassified1) as CustClassified1,

if(isnull(custCOE),'-',custCOE) as custCOE;

ApplyMap('MAP_EmployeeToFullName',CustProposalCoordinator, Null() ) ASCustProposalCoordinator,
CustPastPerformanceLead,
ApplyMap('MAP_EmployeeToFullName',CustProgramManager, Null() ) ASCustProgramManager,
CustStaffingPlanLead,
CustBookBoss,
CustWriter1,
CustWriter2,
CustWriter3,
CustWriter4;

SQL SELECT *

from tablename;