Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I currently use the below code to calculate the time in band of a person based on their latest change in an identical band,
Banding:
LOAD
PERSONID_EXT as "Global ID Banding",
Pers.No.,
"Personnel Number" as "Name Banding",
// "Employment Status",
// "Company Code",
// Position,
// Position1,
"PS group",
"Start Date",
"End Date" as "Band End Date"
FROM []
(ooxml, embedded labels, table is Sheet1);
Temp3:
Load *,
//if(ApplyMap('ScaleMap',AutoNumber(ID&Name&Scale),'')='','',if(previous(Scale)=Scale,'', [Start date])) as ScaleStart
if((Previous("Global ID Banding")="Global ID Banding" and Previous("PS group")="PS group"),'', "Start Date") as ScaleStart
resident Banding
;
ScaleMap2:
mapping load
AutoNumber("Global ID Banding"), Max(ScaleStart) as MaxScaleStart
resident Temp3
group by "Global ID Banding"
;
Final:
Load*, // Time in Band
Year(Today() - "Band Start Date" + 1) - 1900 & ' Years, '
& Num(Month(Today() - "Band Start Date" + 1) - 1) & ' Months ' as "Time in Band";
Load *,
Date(if(ApplyMap('ScaleMap2',AutoNumber("Global ID Banding"),'')="Start Date","Start Date",'')) as "Band Start Date"
resident Banding
;
drop table Banding;
drop table Temp3;
However for some reason Band Start Date is returning the date value and a blank value as seen in the below picture:
Can anyone advise me on how to get rid of this blank duplicate?
I have gone over all my fields, and there do not seem to be any duplicate keys.
The only key that I run is "Global ID Banding" which is linked to the full people file.
Thanks.
Also if you want to retain only the records try adding the map in where clause
Like
Resident Banding
Where
ApplyMap('ScaleMap2',AutoNumber("Global ID Banding"),NULL()))=NUM([Start Date])
;
Can you attach a sample xls file for testing?
Also, try adding Null() instead of '' in all your else statements.
Hi Robin,
hard to say anything without see the source data, but
it seems at the empty rows, like the [Time in band] columns get a value that is a field label 'Years, Months'
I suggest you to see the results set step by step at loading. Load only Banding table and check the data and so on.
G.
Hi,
Attached is a sample.
I have these lines multiplied by thousands.
What the piece of code does, is figure out what the lowest PS Scale is, and then create a time in band based on the 1st entry date into the lowest band.
I have checked everything. But I cannot figure out where I am making a mistake.
Thanks for looking at it.
Hi Robin,
To check your data issue of duplicates I have added rowno() as a column and with provided example I don't see any duplicates.
The blanks are the ones where your applymap() is not equal to your start date.
Modified your code a little for better matching purpose.
Banding:
LOAD
[Global ID Banding],
Pers.No.,
[Name Banding],
[PS group],
[Start Date],
[Band End Date],
RowNo() as Row_Number
FROM
[Time in Band example.xlsx]
(ooxml, embedded labels, table is Sheet1);
Temp3:
Load
*,
if((Previous("Global ID Banding")="Global ID Banding" and Previous("PS group")="PS group"),NULL(), "Start Date") as ScaleStart
Resident Banding
;
ScaleMap2:
Mapping load
AutoNumber("Global ID Banding") as Global_Banding,
Max(ScaleStart) as MaxScaleStart
Resident Temp3
group by
"Global ID Banding"
;
drop table Temp3;
Final:
NoConcatenate
Load
*,
Year(Today() - "Band Start Date" + 1) - 1900 & ' Years, ' & Num(Month(Today() - "Band Start Date" + 1) - 1) & ' Months ' as "Time in Band"
;
Load
*,
Date(if(NUM(ApplyMap('ScaleMap2',AutoNumber("Global ID Banding"),NULL()))=NUM([Start Date]),"Start Date",NULL())) as "Band Start Date"
Resident Banding
;
drop table Banding;
Also if you want to retain only the records try adding the map in where clause
Like
Resident Banding
Where
ApplyMap('ScaleMap2',AutoNumber("Global ID Banding"),NULL()))=NUM([Start Date])
;
Thank you!