topic Data load issue in App Development

Data load issue

robin_heijt — Fri, 05 Oct 2018 14:11:28 GMT

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.

Re: Data load issue

vamsee — Fri, 05 Oct 2018 17:36:23 GMT

Can you attach a sample xls file for testing?

Also, try adding Null() instead of '' in all your else statements.

Re: Data load issue

undergrinder — Mon, 08 Oct 2018 06:54:53 GMT

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.

Re: Data load issue

robin_heijt — Mon, 08 Oct 2018 14:55:50 GMT

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.

Re: Data load issue

vamsee — Mon, 08 Oct 2018 16:49:43 GMT

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;

Re: Data load issue

vamsee — Mon, 08 Oct 2018 16:51:10 GMT

Also if you want to retain only the records try adding the map in where clause

Resident Banding

Where

ApplyMap('ScaleMap2',AutoNumber("Global ID Banding"),NULL()))=NUM([Start Date])

;

Re: Data load issue

robin_heijt — Wed, 10 Oct 2018 08:07:43 GMT

Thank you!