Re: Row Number showing 0 for all records - Qlik Community

robin_heijt · ‎2019-03-22

When I use the following code

RowNo(TOTAL) as RowNumber,

or

RowNo() as RowNumber,

All values for RowNumber will always display '0'.

Any ideas on what might cause this?

sunny_talwar · ‎2019-03-22

Try RecNo() and see if that helps? Also do you have a preceding load after the load where you create a field with RowNo()?

robin_heijt · ‎2019-03-22

Hi Sunny,

RecNo() returns the same.

And no, I don't have a preceding load after. Essentially I have 4 tables joined together. But due to that my count of unique identifier doesn't work anymore as it will show the multiplied count. So I am trying to implement a row count, so I can create a distinct count based on that.

sunny_talwar · ‎2019-03-25

Can you share the exact script you are using to help you better?

robin_heijt · ‎2019-03-25

Hi Sunny,

Join Script:

Taleo_Report:
// LOAD *,  1 AS TableSource;
LOAD
//     "Time Since Creation",
    "Req. Identifier",
    "Title (BL)",
    "# Openings Left To Fill (Hired)",
    "Current Status",
"Position ID",
"Sharp Req. ID",
    "Recruiter Name",
    "ABI_POSITION_BAND Value",
    "ABI_Internal Value",
//     "Submission Transitions Process Type",
//     "Recruiter Name1",
    "Recruiter Email",
    "Hiring Manager Name",
    "Hiring Manager Email",
    "Recruiter Assistant Name",
    "Recruiter Assistant Email",
    "Level 1 - Name",
    "Level 2 - Name",
"Country (TTH)",
"Work Location (TTH)",
    "ABI_COST_CENTER Value",
"Function (TTH)",
    "Candidate Selection Workflow Name",
    "Req. Creation Date",
    "Latest Filled Date",
    "First Sourced After Latest Approved Date",
    "External from date (for Wilson)",
    "Candidate Identifier",
"Name (TTH)",
//     "Is Currently Referred",
//     "Referrer Name",
//     "Referrer Employee Number",
//     "Referrer Email",
    "Submission Is Completed",
    "Submission Is Internal",
    "Submission Medium",
    "Submission Source Type",
    "Submission Source (BL)",
    "Submission Created Date",
    "Submission Completed Date",
    "Minimum Requirements Met",
    "Is Ace",
    "Current Step Name",
    "Current Status Name",
    "Time to Start",
    "New Offer Accepted Date",
    "Application Completed Date",
"Pending CV Screening Date",
"CV Screening Completed Date",
"Pending Phone Screening Date",
"Phone Screening Completed Date",
"Line Manager Review Date",
"Approved for Interview Date",
"SST Link Sent",
"1st Interview Pending Date",
"1st Interview Scheduled Date",
"1st Interview Completed",
"2nd Interview Pending Date",
"2nd Interview Scheduled",
"2nd Interview Completed Date",
"3rd Interview Pending Date",
"3rd Interview Scheduled",
"3rd Interview Completed Date",
"Pre Offer Approved",
"Offer Created Date",
"Hired Date",
"Offer Extended Date",
"Offer to be made Date",
"First Interview Withdrew Date",
 "Second Interview Withdrew Date",
"Third Interview Withdrew Date",
"First Interview Rejected Date",
 "Second Interview Rejected Date",
"Third Interview Rejected Date",
"Documents to be Reviewed Date",
"Contract Available Date",
"To be Hired Date",
"Employee Number"
Resident Taleo;
Drop Table Taleo;



left Join Taleo_Report:
//================================================================================================================
//									Local Entity & Macro Entity & InBev Entity
//================================================================================================================
//  LOAD *,  2 AS TableSource;
LOAD
    ObjectID as "Position ID",
    ObjectID ,
    Org.unit,
    "Organizational Unit",
    "AB-InBev Entity",
    "Inbev Entity Description",
    "InBev Ent L1",
    "Inbev Entity Description1",
    "InBev Ent L2",
    "Inbev Entity Description2",
    "InBev Ent L3",
    "Inbev Entity Description3",
    "InBev Ent L4",
    "Inbev Entity Description4",
    "InBev Ent L5",
    "Inbev Entity Description5",
    "InBev Ent L6",
    "Inbev Entity Description6",
    "Macro Organizational Entity",
    "Macro Entity Description",
    "Mac.Org. Ent. L1",
    "Macro Entity Description1",
    "Mac.Org. Ent. L2",
    "Macro Entity Description2",
    "Mac.Org. Ent. L3",
    "Macro Entity Description3",
    "Mac.Org. Ent. L4",
    "Macro Entity Description4",
    "Mac.Org. Ent. L5",
    "Macro Entity Description5",
    "Mac.Org. Ent. L6",
    "Macro Entity Description6",
    "Local Entity",
    "Local Entity Description",
    "Local Entity L1",
    "Local Entity Description1",
    "Local Entity L2",
    "Local Entity Description2",
    "Local Entity L3",
    "Local Entity Description3",
    "Local Entity L4",
    "Local Entity Description4",
    "Local Entity L5",
    "Local Entity Description5",
    "Local Entity L6",
    "Local Entity Description6"
FROM [lib://People Reporting (eeinterbrew_heijtrob)/OM Report\OM Report Entities.XLSX]
(ooxml, embedded labels, table is Sheet1);

//================================================================================================================
//													Movement Report - See Section
//================================================================================================================

     
Concatenate Taleo_Report:
// LOAD *,  3 AS TableSource;
Load 
//     "HR Administrator",
//     "Global ID",
//     PERNR,
	"Date of the Movement",
//     Date(Date#("Action Begda", 'DD.MM.YYYY'), 'DD/MM/YYYY') as "Date of the Movement",
    "Action Type",
    "Action Reason",
    "Band Before",
    "Position ID Before",
    "Position Name Before EN",
//     "Legacy Position ID Before",
//     "Legacy Dept ID Before",
//     "Legacy Cat Code Before",
    "Cost Center Before",
//     "Function Before",
    "Company Code Before",
    "Local entity 1 Before",
    "Local entity 2 Before",
    "Local entity 3 Before",
    "Local entity 4 Before",
    "Local entity 5 Before",
    "Local entity 6 Before",
    "Macro entity 1 Before",
    "Macro entity 2 Before",
    "Macro entity 3 Before",
    "Macro entity 4 Before",
    "Macro entity 5 Before",
    "Macro entity 6 Before",
    "Band After",
    "Position ID After",
    "Position ID After" as "Position ID",
    "Position Name After EN",
//     "Legacy Position ID After",
//     "Legacy Dept ID After",
//     "Legacy Cat Code After",
    "Cost Center After",
//     "Function After",
    "Company Code After",
    "Local entity 1 After",
    "Local entity 2 After",
    "Local entity 3 After",
    "Local entity 4 After",
    "Local entity 5 After",
    "Local entity 6 After",
    "Macro entity 1 After",
    "Macro entity 2 After",
    "Macro entity 3 After",
    "Macro entity 4 After",
    "Macro entity 5 After",
    "Macro entity 6 After",
    "Effective Date",
    "Movement Type"
     Resident Movements
     Where Date("Effective Date")>='01/01/2018' and Date("Effective Date")<today() 
     ;
     Drop table Movements;
     




left join (Taleo_Report)

EmployeeReport:
// LOAD *,  4 AS TableSource;

LOAD
"Global ID",
"Personal ID",
"Name",
Gender,
"Country of Birth Initials",
"Country of Birth",
"Nationality",
"Language key",
Country,
"Country Key for VP-1",
"Country Key Macro",
    "EBM Level",
"EBM Level Sub",
"LM",
"LM Global ID",
"LM Personal ID",
"LM Position",
"Original hire date",
"Date of birth",
"Position ID",
"Position",
"Bandpreclean",
"Band Position",
"Grandfathered Band",
"Grandfathering Start Date",
"Grandfathering End Date",
"Employee Group ID",
"Employee Group",
"AB-InBev Entity ID",
    "AB-InBev Entity Text",
"Macro Organizational Entity ID",
    Mac.Org.En1,
"Company Code",
"Employee Status ID",
"Employee Status",
"Employee subgroup ID",
"Employee subgroup",
    Org.unit,
"Contract Type ID",
"Contract Type",
"Cost Center",
    Expat,
"Home Country",
"Maritial Status",
Children


Resident FinalEMReport;
Drop Table FinalEMReport;

Final Script:

Final_Report:
NoConcatenate
LOAD *,  1 AS TableSource;
Load*,
If("Submission Is Internal"='Yes' and "ABI_Internal Value"='No','Internal with Fee',
If("Submission Is Internal"='Yes','Internal',
If("Submission Is Internal"='No', 'External'))) as "Internal/External";



Load*,
If(Year(date(RangeMin([Latest Filled Date],[New Offer Accepted Date]))),Year(date(RangeMin([Latest Filled Date],[New Offer Accepted Date]))),
If(Year([Effective Date]),Year([Effective Date]))) as "Filled/Offer Accepted Year",

Date(If(date(RangeMin([Latest Filled Date],[New Offer Accepted Date])),date(RangeMin([Latest Filled Date],[New Offer Accepted Date])),
If([Effective Date],[Effective Date]))) as "Filled/Offer Accepted Date",

If(Month(date(RangeMin([Latest Filled Date],[New Offer Accepted Date]))),Month(date(RangeMin([Latest Filled Date],[New Offer Accepted Date]))),
If(Month([Effective Date]),Month([Effective Date]))) as "Filled/Offer Accepted Month";

Load *,
if(IsNull("Macro Entity Description"),'No Macro Entity',
If("Employee Status ID"='3' and "Employee Group" ='White Collar' and("Macro Entity Description2" ='ZONE EUROPE') and not "Band Unified"='N/A' and not "Band Unified"='ALL', 'EP',
If("Employee Status ID"='3' and "Employee Group" ='Executives(Bd I-VII)' and("Macro Entity Description2" ='ZONE EUROPE')and not "Band Unified"='N/A' and not "Band Unified"='ALL', 'EP',
('Out Of Structure') )))as Scope
;

Load*,
 if(match(purgechar(Bandpreclean,'-AB'),'I','II', 'III', 'IV', 'V', 'VI', 'VII')>0, purgechar(Bandpreclean,'-AB'),
If(IsNull(Bandpreclean), 'N/A', 'ALL')) as "Band Unified";

Load*,
If("Employee Status ID"='3' and "Employee Group" ='White Collar' and("Macro Entity Description2" ='ZONE EUROPE') , 'EP',
If("Employee Status ID"='3' and "Employee Group" ='Executives(Bd I-VII)' and("Macro Entity Description2" ='ZONE EUROPE'), 'EP',
('Out of EP') ))as EP
;
Load*,
If([Time to Fill]<='60',dual('0-60 Days',1),
If([Time to Fill]>'60' and [Time to Fill]<='90',dual('61-90 Days',2),
If([Time to Fill]>'90',dual('>90 Days',3)))) as "Time to Fill Grouped"
;
Load*,
If([Time to Fill]<='45',dual('0-45 Days',1),
If([Time to Fill]>'45' and [Time to Fill]<='60',dual('45-60 Days',2),
If([Time to Fill]>'60' and [Time to Fill]<='90',dual('60-90 Days',3),
If([Time to Fill]>'90',dual('>90 Days',4)
// If("# Filled Positions"="# Filled Positions",dual("# Filled Positions",5),
// If("Time to Fill"="Time to Fill",dual(avg("Time to Fill"),6)

)))) as "Time to Fill Grouped Small"
;
Load*,
IF(Len([New Offer Accepted Date])>0 or len([Latest Filled Date])>0 or [Movement Type]='Promotion Band Up' or [Movement Type]='Promotion Within Band' or [Movement Type]='Lateral Move', "Position ID") as "# Filled Positions"
;



// IF([Movement Type]='Promotion Band Up' or [Movement Type]='Promotion Within Band' or [Movement Type]='Lateral Move','0',

// // IF(len([Latest Filled Date])>0 and len([New Offer Accepted Date])>0,
// date(RangeMin([Latest Filled Date],[New Offer Accepted Date]))-[Req. Creation Date]

// // IF(previous("Req. Identifier")="Req. Identifier" and len([Latest Filled Date])>0 and len([New Offer Accepted Date])>0, null()
// )




Load*,

IF([Movement Type]='Promotion Band Up' or [Movement Type]='Promotion Within Band' or [Movement Type]='Lateral Move','0',

// IF(len([Latest Filled Date])>0 and len([New Offer Accepted Date])>0,
date(RangeMin([Latest Filled Date],[New Offer Accepted Date]))-[Req. Creation Date]

// IF(previous("Req. Identifier")="Req. Identifier" and len([Latest Filled Date])>0 and len([New Offer Accepted Date])>0, null()
)
as "Time to Fill";

//###########################################################################################################################
//														Final:
//###########################################################################################################################


Load
//     "Time Since Creation",
    IF(isnull("Req. Identifier"),'Internal' ,"Req. Identifier") as "Req. Identifier",
    "Title (BL)",
    "# Openings Left To Fill (Hired)",
    "Current Status",
"Position ID",
"Sharp Req. ID",
    "Recruiter Name",
    "ABI_POSITION_BAND Value",
    "ABI_Internal Value",
//     "Submission Transitions Process Type",
//     "Recruiter Name1",
    "Recruiter Email",
    "Hiring Manager Name",
    "Hiring Manager Email",
    "Recruiter Assistant Name",
    "Recruiter Assistant Email",
    "Level 1 - Name",
    "Level 2 - Name",
"Country (TTH)",
"Work Location (TTH)",
    "ABI_COST_CENTER Value",
"Function (TTH)",
    "Candidate Selection Workflow Name",
    "Req. Creation Date",
    "Latest Filled Date",
    "First Sourced After Latest Approved Date",
    "External from date (for Wilson)",
    "Candidate Identifier",
"Name (TTH)",
//     "Is Currently Referred",
//     "Referrer Name",
//     "Referrer Employee Number",
//     "Referrer Email",
    "Submission Is Completed",
    "Submission Is Internal",
    "Submission Medium",
    "Submission Source Type",
    "Submission Source (BL)",
    "Submission Created Date",
    "Submission Completed Date",
    "Minimum Requirements Met",
    "Is Ace",
    "Current Step Name",
    "Current Status Name",
    "Time to Start",
    "New Offer Accepted Date",
//     "New Offer Accepted Date" as "New Offer Accepted Date TIP",
//     IF([New Offer Accepted Date]>=[Latest Filled Date],

// 	   IF(Previous("Req. Identifier")="Req. Identifier" and isnull([New Offer Accepted Date]),previous([New Offer Accepted Date]),[New Offer Accepted Date]) AS "New Offer Accepted Date",
    "Application Completed Date",
"Pending CV Screening Date",
"CV Screening Completed Date",
"Pending Phone Screening Date",
"Phone Screening Completed Date",
"Line Manager Review Date",
"Approved for Interview Date",
"SST Link Sent",
"1st Interview Pending Date",
"1st Interview Scheduled Date",
"1st Interview Completed",
"2nd Interview Pending Date",
"2nd Interview Scheduled",
"2nd Interview Completed Date",
"3rd Interview Pending Date",
"3rd Interview Scheduled",
"3rd Interview Completed Date",
"Pre Offer Approved",
"Offer Created Date",
"Hired Date",
"Offer Extended Date",
"Offer to be made Date",
"First Interview Withdrew Date",
 "Second Interview Withdrew Date",
"Third Interview Withdrew Date",
"First Interview Rejected Date",
 "Second Interview Rejected Date",
"Third Interview Rejected Date",
"Documents to be Reviewed Date",
"Contract Available Date",
"To be Hired Date",
"Employee Number",

    ObjectID ,
    "Organizational Unit",
    "AB-InBev Entity",
    "Inbev Entity Description",
    "InBev Ent L1",
    "Inbev Entity Description1",
    "InBev Ent L2",
    "Inbev Entity Description2",
    "InBev Ent L3",
    "Inbev Entity Description3",
    "InBev Ent L4",
    "Inbev Entity Description4",
    "InBev Ent L5",
    "Inbev Entity Description5",
    "InBev Ent L6",
    "Inbev Entity Description6",
    "Macro Organizational Entity",
    "Macro Entity Description",
    "Mac.Org. Ent. L1",
    "Macro Entity Description1",
    "Mac.Org. Ent. L2",
    "Macro Entity Description2",
    IF("Macro Entity Description2"='ZONE EUROPE' and isnull("Macro Entity Description3"),'ZONE', "Macro Entity Description3") as BU,
    "Mac.Org. Ent. L3",
    "Macro Entity Description3",
    "Mac.Org. Ent. L4",
    "Macro Entity Description4",
    "Mac.Org. Ent. L5",
    "Macro Entity Description5",
    "Mac.Org. Ent. L6",
    "Macro Entity Description6",
    "Local Entity",
    "Local Entity Description",
    "Local Entity L1",
    "Local Entity Description1",
    "Local Entity L2",
    "Local Entity Description2",
    "Local Entity L3",
    "Local Entity Description3",
    "Local Entity Description3" as Function,
    "Local Entity L4",
    "Local Entity Description4",
    "Local Entity Description4" as SubFunction,
    "Local Entity L5",
    "Local Entity Description5",
    "Local Entity L6",
    "Local Entity Description6",
    
    //     "HR Administrator",
    "Global ID",
//     PERNR,
	"Date of the Movement",
//     Date(Date#("Action Begda", 'DD.MM.YYYY'), 'DD/MM/YYYY') as "Date of the Movement",
    "Action Type",
    "Action Reason",
    "Band Before",
    "Position ID Before",
    "Position Name Before EN",
//     "Legacy Position ID Before",
//     "Legacy Dept ID Before",
//     "Legacy Cat Code Before",
    "Cost Center Before",
//     "Function Before",
    "Company Code Before",
    "Local entity 1 Before",
    "Local entity 2 Before",
    "Local entity 3 Before",
    "Local entity 4 Before",
    "Local entity 5 Before",
    "Local entity 6 Before",
    "Macro entity 1 Before",
    "Macro entity 2 Before",
    "Macro entity 3 Before",
    "Macro entity 4 Before",
    "Macro entity 5 Before",
    "Macro entity 6 Before",
    "Band After",
    "Position ID After",
    "Position Name After EN",
//     "Legacy Position ID After",
//     "Legacy Dept ID After",
//     "Legacy Cat Code After",
    "Cost Center After",
//     "Function After",
    "Company Code After",
    "Local entity 1 After",
    "Local entity 2 After",
    "Local entity 3 After",
    "Local entity 4 After",
    "Local entity 5 After",
    "Local entity 6 After",
    "Macro entity 1 After",
    "Macro entity 2 After",
    "Macro entity 3 After",
    "Macro entity 4 After",
    "Macro entity 5 After",
    "Macro entity 6 After",
        "Effective Date",
    "Movement Type",
    

"Personal ID",
"Name",
Gender,
"Country of Birth Initials",
"Country of Birth",
"Nationality",
"Language key",
Country,
"Country Key for VP-1",
"Country Key Macro",
    "EBM Level",
"EBM Level Sub",
"LM",
"LM Global ID",
"LM Personal ID",
"LM Position",
"Original hire date",
"Date of birth",
"Position",
"Bandpreclean",
"Band Position",
"Grandfathered Band",
"Grandfathering Start Date",
"Grandfathering End Date",
"Employee Group ID",
"Employee Group",
"AB-InBev Entity ID",
    "AB-InBev Entity Text",
"Macro Organizational Entity ID",
    Mac.Org.En1,
"Company Code",
"Employee Status ID",
"Employee Status",
"Employee subgroup ID",
"Employee subgroup",
    Org.unit,
"Contract Type ID",
"Contract Type",
"Cost Center",
    Expat,
"Home Country",
"Maritial Status",
Children
// TableSource


     
     Resident Taleo_Report;
// Where  "Employee Number"<>"Personal ID";
     Drop Table Taleo_Report;

sunny_talwar · ‎2019-03-25

I tried to look through, but couldn't find it... where is the RowNo() being used in the above script?

robin_heijt · ‎2019-03-25

No need anymore.

I used one of your other topics, and I was able to use the AGGR function to achieve my goal. Thanks anyway.

sunny_talwar · ‎2019-03-25

Oh okay...cool