Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
please help to load the script from file:
1. I need to load latest available date "realization" rate from input data in terms of "job codes"
Please see below input file from which we need select out the maximum available date.
Hope on your assistance.
So what output are you expecting?
Do you only want to load data for latest date?
then try below
MaxDT:
LOAD Max( Period) as MaxPeriod
FROM
[xxxxxx\Realizations by month.xlsx]
(ooxml, embedded labels, table is Sheet1);
LOAD [Job code],
Realisation,
Period
FROM
[xxxxx\Realizations by month.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where Exists(MaxPeriod,Period);
////////////////////////////////////////////////////////////
OR using KEEP keyword
/////////////////////////////////////////////////////////
MaxDT:
LOAD Max( Period) as Period
FROM
(ooxml, embedded labels, table is Sheet1);
LEFT KEEP
LOAD [Job code],
Realisation,
Period
FROM
(ooxml, embedded labels, table is Sheet1);
Drop table MaxDT;
How about this:
Realization:
LOAD [Job code],
Realisation,
Date(Period, 'MMM-YYYY') as Period
FROM
(ooxml, embedded labels, table is Sheet1);
INNER JOIN (Realization)
LOAD [Job code],
Max(Period) as Period
RESIDENT Realization
WHERE Realisation > 0
GROUP BY [Job code]
;
You have to try this ways with your "JobCodes"
Source:
LOAD [Job code],
Realisation,
Date(Period) as Period
FROM
[Realizations by month.xlsx]
(ooxml, embedded labels, table is Sheet1);
MaxTable:
LOAD
[Job code],
Date(Max(Period)) as MaxDate
Resident Source
Group By [Job code]
Order By [Job code];
NoConcatenate
FinalTable:
LOAD
*
Resident Source Where Exists(MaxDate,Period);
DROP Tables Source,MaxTable;
I need the realization rate out of maximum date for each job code.
For example,
Code Date Realization rate
1 1 January 20%
1 1 February 30%
Output shoul be like
Code Date Rellization rate
1 1 February 30%
See my code below, it will load the latest data per [Job Code]
Marcus, next step, I need to map your script to Advisory table?
Could you please give some advice how to do it more efficiently? Now I have error.
Here below my script
Realization:
LOAD [Job code],
Realisation,
Period
FROM
[Data\Realizations by month.xlsx]
(ooxml, embedded labels, table is Sheet1);
INNER JOIN (Realization)
LOAD [Job code],
Max(Period) as Period
RESIDENT Realization
WHERE Realisation > 0
GROUP BY [Job code]
Realizations:
LOAD
Realisation,
[Job code]&'_'&Period as KEY
FROM [Data\Realizations by month.xlsx] (ooxml, embedded labels, table is Sheet1);
MapRealization:
Mapping
LOAD Distinct KEY, Realisation
Resident Realizations;
Advisory:
LOAD BU,
[Client name],
[Job code],
[Eng. name],
[Work dt],
Period,
Month(Period) as Date,
QuarterStart(Period) as Quarter,
Hrs,
Tr.Reference,
Tr.Type,
Descript,
Ref.,
Entity,
[Tr type],
[Journ Type],
[Empl code],
[Empl name],
[Empl office],
[Empl dep],
[Eng CC],
EngMan,
[Job code]&'_'&Period as KEY,
ApplyMap('MapRealization', [Job code]&'_'&Period,40) as AdjRealization
FROM [Data\July'17 ch hours details_kz _emc.xls] (biff, embedded labels, table is Advisory$);
You're missing a semi-colon after the INNER JOIN
INNER JOIN (Realization)
LOAD [Job code],
Max(Period) as Period
RESIDENT Realization
WHERE Realisation > 0
GROUP BY [Job code]
;
OK
Create a Straight table
Dimension
Job Code
Period
Expression
max( TOTAL <[Job code]> Realisation)