Invalid expression using resident

[sutoniusahadong]

Hi

I have problem using resident

RKTK :
LOAD [Company ID] as COMP_ID,
[Company ID]&[Position Id] AS ID_MP,
[Company ID]&[Position Id]&[Start Date] as ID_MP_DATE, [Company ID]&[Company Office]AS ID_SM,
[Position Id],
[Company Office],
[Company Office Group],
[Start Date],
[End Date],
[Total Target Head Count (x)],
[Target Head Count Adjustment (y)],
[Adjusted Total Target Head Count (z) = (x + y)]

FROM
[D:\BI\SOURCE TEST BI\RKTK.xlsx]
(ooxml, embedded labels, table is rktk);

Left Join

LOAD PT&[Position ID] AS ID_MP,

Level as LEVEL_POS,
[Unit Kerja],
[Valid From],
[Valid To],
[Unit Kerja1]
FROM
[D:\BI\SOURCE TEST BI\POS_ASSIGNMENT.xlsx]
(ooxml, embedded labels, table is POSISI);

Left Join
LOAD
PT&CO AS ID_SM,

CBG,
[CBG For Fulfillment],
ROM,
HRBP
FROM
[D:\BI\SOURCE TEST BI\POS_ASSIGNMENT.xlsx]
(ooxml, embedded labels, table is CBGROMHRBP);

RKTK_POS_PERBULAN:
LOAD

ID_MP_DATE,

[Start Date],
SUM([Total Target Head Count (x)])AS RKTK_APPROVE1,
SUM([Target Head Count Adjustment (y)]) AS RKTK_ADJUSTED,
SUM([Adjusted Total Target Head Count (z) = (x + y)])AS RKTK_TARGET_ADJUSTED

Resident RKTK Group By ID_MP;

Left Join
LOAD
COMP_ID,
ID_MP,
ID_SM,
[Company Office],
CBG,
[CBG For Fulfillment],
ROM,
HRBP,
[Unit Kerja],
[Unit Kerja1]

RESIDENT RKTK ;

Drop Table RKTK;

LOAD PT,
PT&[Position ID]AS ID_MP,
[Position ID] AS [Position Id],
[Position Title],
[Valid From] AS VALIDFROM_BPOSITION,
[Valid To] AS VALIDTO_BPOSITION,
[Position Summary],
[Direct Superior],
[Position Class] as PC,
[Job ID],
[Job Title],
[Organization Code],
[Organization Name],
[Transfer Group],
[Time Format]
FROM
[D:\BI\SOURCE TEST BI\GOAL.xlsx]
(ooxml, embedded labels, table is B_POSITION);

Left Join
LOAD pc AS PC,
level_mercer,
level_int,
keterangan
FROM
[D:\BI\SOURCE TEST BI\RKTK.xlsx]
(ooxml, embedded labels, table is LEVEL_POSISI);

When run Reload there is error like this : 

RKTK_POS_PERBULAN << RKTK
Error: Invalid expression

Tq

Regards,

Toni

[joseph_morales · Accepted solution]

hi @sutoniusahadong ,

You should group by all non-calculated fields in RKTK_POS_PERBULAN table:

RKTK_POS_PERBULAN:
LOAD

ID_MP_DATE,

[Start Date],
SUM([Total Target Head Count (x)])AS RKTK_APPROVE1,
SUM([Target Head Count Adjustment (y)]) AS RKTK_ADJUSTED,
SUM([Adjusted Total Target Head Count (z) = (x + y)])AS RKTK_TARGET_ADJUSTED

Resident RKTK Group ID_MP_DATE,[Start Date];

Or add ID_MP in resident table:

RKTK_POS_PERBULAN:
LOAD

ID_MP_DATE,

[Start Date],

ID_MP,
SUM([Total Target Head Count (x)])AS RKTK_APPROVE1,
SUM([Target Head Count Adjustment (y)]) AS RKTK_ADJUSTED,
SUM([Adjusted Total Target Head Count (z) = (x + y)])AS RKTK_TARGET_ADJUSTED

Resident RKTK Group  ID_MP,ID_MP_DATE,[Start Date];

Regards