Merge two rows information into one row

[muhammadraza]

Hello Guys,

As you can see (for Task1) the information is divided into two rows, can we make it appear into one row, I don't want the activity name to be repeated, all information regarding each activity should be shown into single row.

PFA are the source files.

Regards,

Raza

[sunny_talwar]

Not sure how it will work with other selections, but for current selection it seems to be working. Check it out

[ziadm]

Hi

Create a unique key using AutoNumberHash128(TaskID,ProjID)

and aggregate your load statement to load one row per taskid

// This is my approach

Actual_Plan_Fcst:

LOAD

     AutoNumberHash128(PROJ_ID, TASK_ID) as TaskNewKey,

     PROJ_SHORT_NAME,

     TASK_CODE,

     TASK_NAME,

     RSRC_NAME,

     RSRC_SHORT_NAME,

     STARTDATE,

     KeyDate,

     ENDDATE,

     ACTUALUNITS,

     ACTUALUNITS_FLAG,

     REMAININGUNITS,

     PLANNEDUNITS,

     PARENT_RSRC_NAME,

     TASK_ID,

     RES_CAPCITY,

     ACT_START_DATE,

     ACT_END_DATE,

     B_TARGET_WORK_QTY,

     B_R_TARGET_START_DATE,

     B_R_TARGET_END_DATE,

     PROJECT_DESCRIPTION,

     AREA,

     ORIGINAL_DURATION,

     STATUS_CODE,

     REMARKS,

     IRN_ACCEPTANCE,

     Source

FROM

C:\nPrinting\Planning\test.xlsx

(ooxml, embedded labels, table is test);

NoConcatenate

Plan_Fcst:

LOAD TaskNewKey

     Resident

     Actual_Plan_Fcst

     Group by

     TaskNewKey;

     left Join

     LOAD

     TaskNewKey,

     Maxstring(PROJ_SHORT_NAME),

     Maxstring(TASK_CODE),

     Maxstring(TASK_NAME),

     Maxstring(RSRC_NAME)

// You aggregate all you other fields using sum or max aggregation functions

     Resident

     Actual_Plan_Fcst

     Group By PROJ_SHORT_NAME,TASK_CODE,TASK_NAME,RSRC_NAME;

     DROP Table Actual_Plan_Fcst;