if statement error in load script, using preceeding load and left join

[#1]

I have issue with the following script where I've used a left join to map a value into my main table and then using if statement on the mapped field within the load statement. 

The purpose of the data (which I am showing in a table) is to show work requests which are assigned or not assigned to an engineer.  My main table show the work request details, in the script the table I've used the left join on pulls in the work request number where its assigned to an engineer.  I'm trying to create a field (dimension) value which simply says if the work request is "assigned" or "unassigned", so I can then apply simple filtering to the job list of work requests.

Can anyone point out what's wrong with the script?

My script, error message and data table are shown below:

WorkRequests:

LOAD *, if(isnull([CF WR Code]) ,'UnAssigned' , 'Assigned') as [WR Assigned?];

LOAD Priority,
ordering_seq,
[Problem Type],
[WR Site Code],
status_desc,
status_cat,
escalated_completion_YN,
escalated_response_YN,
[Actual Labour Hours],
[Action Item ID],
[Building Code],
[Cause Code],
[Craftspersons notes],
[Completed By],
[Estimated Total Cost],
[Total Cost],
[Date to Perform],
[Date Closed],
[Date Completed],
[Date of Completion Escalation],
[Date of Response Escalation],
[Date Requested],
[Work Description],
[Equipment Code],
[Escalated for Completion],
[Escalated for Response],
[Estimated Labour Hours],
[Floor Code],
[PM Procedure],
[PM Schedule Code],
[Repair Type],
[Requested By],
[Room Code],
[Work Request Status],
[Time to Perform],
[Time Completed],
[Time Requested],
[Primary Trade Required],
[Work Order Code],
[Work Request Code],
[Oracle Number BPN/P6],
[PMP NSR Cost],
[Priority Label]
FROM
Y:\Qlikview_Archibus\QVD\WorkRequests.qvd
(qvd);

left Join (WorkRequests)
LOAD
[Work Request Code],
[Work Request Code] as [CF WR Code]
from Y:\Qlikview_Archibus\QVD\WorkRequestLabourAssignments.qvd (qvd);

[m_woolf · Accepted solution]

The field is not found because the join hasn't occurred yet. Remove the preceding load.

After the join, you can do a resident load something like:

WorkRequestsTemp:

LOAD

    *,

    if(isnull([CF WR Code]) ,'UnAssigned' , 'Assigned') as [WR Assigned?]

resident WorkRequests;

drop table WorkRequests;

rename table WorkRequestsTemp to WorkRequests;

[pokassov]

t1:

Mapping

LOAD
[Work Request Code],
[Work Request Code] as [CF WR Code]
from Y:\Qlikview_Archibus\QVD\WorkRequestLabourAssignments.qvd (qvd);

LOAD *, if(isnull([CF WR Code]) ,'UnAssigned' , 'Assigned') as [WR Assigned?];

LOAD Priority,
ordering_seq,
[Problem Type],
[WR Site Code],
status_desc,
status_cat,
escalated_completion_YN,
escalated_response_YN,
[Actual Labour Hours],
[Action Item ID],
[Building Code],
[Cause Code],
[Craftspersons notes],
[Completed By],
[Estimated Total Cost],
[Total Cost],
[Date to Perform],
[Date Closed],
[Date Completed],
[Date of Completion Escalation],
[Date of Response Escalation],
[Date Requested],
[Work Description],
[Equipment Code],
[Escalated for Completion],
[Escalated for Response],
[Estimated Labour Hours],
[Floor Code],
[PM Procedure],
[PM Schedule Code],
[Repair Type],
[Requested By],
[Room Code],
[Work Request Status],
[Time to Perform],
[Time Completed],
[Time Requested],
[Primary Trade Required],
[Work Order Code],
[Work Request Code],

ApplyMap('t1',[Work Request Code],null())     As [CF WR Code],
[Oracle Number BPN/P6],
[PMP NSR Cost],
[Priority Label]
FROM
Y:\Qlikview_Archibus\QVD\WorkRequests.qvd
(qvd);

[#4]

Hi Dan,

your large field list loads first, then the preceding load of that table runs, and finally the left join at the end.

So during your preceding load, the [CF WR Code] field does not exist, as the left join hasn't happened yet to bring it in.

hope that helps

Joe

[sasiparupudi1]

Use a resident load

LOAD *, if(isnull([CF WR Code]) ,'UnAssigned' , 'Assigned') as [WR Assigned?] Resident eoklist;

Drop table worklist;

Hth

Sasi

[marcus_sommer]

I think the reason ist that qv want to join with the origin field and not with the new alias in the join-table. Try this:

WorkRequests:
LOAD *, if(isnull([CF WR Code]) ,'UnAssigned' , 'Assigned') as [WR Assigned?],

     [CF WR Code] as [Work Request Code];

....

whereas it's unclear what you want to join - join-field and extra-field are the same ...

- Marcus

[#7]

Thank you, script now reads as (and appears to work as I need) :

WorkRequests:

LOAD Priority,
ordering_seq,
[Problem Type],
[WR Site Code],
status_desc,
status_cat,
escalated_completion_YN,
escalated_response_YN,
[Actual Labour Hours],
[Action Item ID],
[Building Code],
[Cause Code],
[Craftspersons notes],
[Completed By],
[Estimated Total Cost],
[Total Cost],
[Date to Perform],
[Date Closed],
[Date Completed],
[Date of Completion Escalation],
[Date of Response Escalation],
[Date Requested],
[Work Description],
[Equipment Code],
[Escalated for Completion],
[Escalated for Response],
[Estimated Labour Hours],
[Floor Code],
[PM Procedure],
[PM Schedule Code],
[Repair Type],
[Requested By],
[Room Code],
[Work Request Status],
[Time to Perform],
[Time Completed],
[Time Requested],
[Primary Trade Required],
[Work Order Code],
[Work Request Code],
[Oracle Number BPN/P6],
[PMP NSR Cost],
[Priority Label]
FROM
Y:\Qlikview_Archibus\QVD\WorkRequests.qvd
(qvd);

left Join (WorkRequests)
LOAD
[Work Request Code],
[Work Request Code] as [CF WR Code]
from Y:\Qlikview_Archibus\QVD\WorkRequestLabourAssignments.qvd (qvd);

WorkRequestTemp:
LOAD *, if(isnull([CF WR Code]) ,'UnAssigned' , 'Assigned') as [WR Assigned?]
Resident WorkRequests;
DROP Table WorkRequests;
RENAME Table WorkRequestTemp to WorkRequest;