Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
robin_heijt
Creator
Creator

Data load issue

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:

Band issue.PNG

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.

1 Solution

Accepted Solutions
vamsee
Specialist
Specialist

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])

;

View solution in original post

6 Replies
vamsee
Specialist
Specialist

Can you attach a sample xls file for testing?

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

undergrinder
Specialist II
Specialist II

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.

robin_heijt
Creator
Creator
Author

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.

vamsee
Specialist
Specialist

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; 

vamsee
Specialist
Specialist

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])

;

robin_heijt
Creator
Creator
Author

Thank you!