Lookup function for script

[#1]

Hi guys

I have a crosstable load , where it has staff names , work day and work status

Looks a bit like this

CROSSTABLE

Working day	Staff	Work Status
01/01/2011	Ann	Half Day Holiday
01/01/2011	Barry	Working
01/01/2011	Nuala	Working
01/01/2011	Fred	Working
01/01/2011	John	Sick
02/01/2011	Ann	Working
02/01/2011	Barry	Working
02/01/2011	Nuala	Working
02/01/2011	Fred	Working
02/01/2011	John	Sick
03/01/2011	Ann	Working
03/01/2011	Barry	Working
03/01/2011	Nuala	Working
03/01/2011	Fred	Working
03/01/2011	John	Working

But I have another load - which is a table of when staff have left - so Barry left on 1/1/11

LEAVETABLE

Staff	End Date
Ann
Barry	01/01/2011
Nuala
Fred
John
Ann
Barry
Nuala
Fred
John
Ann
Barry
Nuala
Fred
John

I would like to be able to

On the CROSSTABLE look up the staff  on the

LEAVETABLE and if the End date is the same or less than Workdate then change workstatus to Left :

Working day	Staff	Work Status
01/01/2011	Ann	Half Day Holiday
01/01/2011	Barry	LEFT
01/01/2011	Nuala	Working
01/01/2011	Fred	Working
01/01/2011	John	Sick
02/01/2011	Ann	Working
02/01/2011	Barry	LEFT
02/01/2011	Nuala	Working
02/01/2011	Fred	Working
02/01/2011	John	Sick
03/01/2011	Ann	Working
03/01/2011	Barry	LEFT
03/01/2011	Nuala	Working
03/01/2011	Fred	Working
03/01/2011	John	Working

Can anyone help with this please ?

[Miguel_Angel_Baeyens]

Hi,

Use a mapping table with the Staff and the End Date, and use it after the crosstable load, something like

LeftMap:
MAPPING LOAD Staff,
     "End Date"
FROM LeftStaff.qvd (qvd);
FinalTable:
LOAD "Working Day",
     Staff,
     If("Working Day" <= ApplyMap('LefMap', Staff), "Work Status", 'Left') AS "Work Status"
RESIDENT Crosstable;
DROP TABLE Crosstable;

Note the single and double quotes.

Hope that helps.

Miguel

[#3]

Hi Miguel

I tried the above, see script pasted in but QV does not seem to have Workstatus at all now- Can you advise please ? I think its because its trying to map on a Crosstable load< No errors are showing on the load :

LOAD Name as Staff,
     [Staff #],
     Start,
     End as [End Date],
     Unit,
     Status,
     [C/Fwd],
     Spec,
     Allow,
     BH,
     Total
    
FROM

(biff, embedded labels, header is 1 lines, table is [Ex staff$]);

Crosstable:
Crosstable (Staff,WorkStatus)
LOAD
Date as [Working Day],
     [Murphy Anne],
     [Boyle Barry],
     [Dwyer Tom],
     [Hope Fred],
     [Martin Larry]
    
FROM

(biff, embedded labels, table is Holidays$)
Where Date>'06/03/2011';

LeftMap:
MAPPING LOAD  Name,
             "End"
FROM
(biff, embedded labels, header is 1 lines, table is [Ex staff$]);

FinalTable:
LOAD "Working Day",
     Staff,
     If("Working Day" <= ApplyMap('LeftMap', Staff), "WorkStatus", 'Gone') AS "WorkStatus"
RESIDENT Crosstable;

DROP TABLE Crosstable;

LOAD Team,
     Managers
FROM

(biff, embedded labels, table is Managers$);

LOAD Staff,
     Team
FROM

(biff, embedded labels, table is Map$);

LOAD WorkStatus,
     Here
FROM

(biff, embedded labels, table is [Work Map$]);

[#4]

sorry I meant to add the map ... Workstatus is her but as part of another load "Work Map"

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

At a first glance, your code seems to be OK. Once the Crosstable load has finished, the table has the same properties as any other table, so you can use it as a resident table just fine. The mapping table from the excel file should work right as well.

But what may be happening is that the "FinalTable" is being concatenated to the "Crosstable", because they have the same number of fields and all fields are named the same. Note that automatic concatenation works in any table in the script, regardless where the original table is, and not only with the previous table. After that, you are DROPping "Crosstable" that now has all records from original crosstable load and applymap table (they are only one table).

To avoid that happening, just put NOCONCATENATE before the LOAD in that table, and that would do:

FinalTable:
NOCONCATENATE LOAD "Working Day",
     Staff,
     If("Working Day" <= ApplyMap('LeftMap', Staff), "WorkStatus", 'Gone') AS "WorkStatus"
RESIDENT Crosstable;

Hope that helps.

Miguel

[#6]

Thanks Miguel

I think its working AOK now , just need to verify the data but the table and values are looking much better

Thanks

A