Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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
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
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$]);
sorry I meant to add the map ... Workstatus is her but as part of another load "Work Map"
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
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