Concat Multiple Fields into One

[RedDead]

Hi,

Im looking for some help loading data into one line/cell. I have the following table (see below) and at the moment it is loading a line for each JOB_ITEM_NO producing multiple lines per JOB_ID. What i want to achieve is for the table to have one line per JOB_ID and the create one field holding the rest of the data. 

Example of what i need would be a field containing all JOB_ITEM_NO, SOR_CODE,  SOR DESCRIPTION and SOR_QTY for example:-

0001,SUBEL01,SUBCON WORK,165 ; 002,V00029,VOID PRE INSPECTION,1; 003,V0003,Electrical pre, 1; etc etc

In the script the table where all the fields are is called FACT_SOR if this helps

Thanks

Alex

[MayilVahanan]

Hi @RedDead 

May be try like below

Load JOB_ID, Concat(JOB_ITEM_NO&','& SOR_CODE&','& SOR DESCRIPTION &','&SOR_QTY, ';') as CombineField

resident FACT_SOR  group by JOB_ID;

In front end, you can check the field "CombineField" .

Hope it helps

[RedDead]

Hi @MayilVahanan 

Thanks for your response. I think i understand. Just to clarify here is a copy of this script:-

SoR:
LOAD
*,
//APPLYMAP('MAP_TEMP_SOR_COMP',SOR_JOB_NUMBER&'|'&JOB_ITEM_NO,NULL()) AS SOR_COMPLETION_DATE,
IF(MATCH(JOB_ITEM_STATUS,0,1,2),1,0) AS SOR_OPEN_COUNTER,
IF(MATCH(JOB_ITEM_STATUS,3),1,0) AS SOR_CLOSED_COUNTER,
IF(SOR_VOID_MAP='1' AND JOB_ITEM_STATUS='3',1,0) AS SOR_CLEAN_MAP,
IF(SOR_VOID_MAP='2' AND JOB_ITEM_STATUS='3',1,0) AS SOR_CLEAR_MAP,
IF(SOR_VOID_MAP='3' AND JOB_ITEM_STATUS='3',1,0) AS SOR_ETEST_MAP
;
LOAD
APPLYMAP('MAP_JOB_NUMBER',SK_JOBKEY,null()) as SOR_JOB_NUMBER,
APPLYMAP('MAP_JOB_RESOURCE_TYPE_SOR',SK_JOBKEY&'-'&JOB_ITEM_NO,0) AS SOR_RESOURCE_TYPE,
APPLYMAP('MAP_JOB_RESOURCE_DESC_SOR',SK_JOBKEY&'-'&JOB_ITEM_NO,0) AS SOR_RESOURCE_DESC,
APPLYMAP('MAP_TRADE_TYPE',TRADE_CODE,'NA') AS SOR_TRADE_DESC,
APPLYMAP('MAP_JOB_ORIG_SOR',SK_JOBKEY&'|'&SK_ITEMCREATIONDATEKEY,NULL()) AS SOR_JOB_ORIG_FLAG,
SK_ITEMCREATIONDATEKEY,
ITEMCREATIONDATE AS SOR_CREATED_DATE,
SK_ITEMUPDATEDDATEKEY,
ITEMUPDATEDDATE,
SOR_QTY_ORDERED,
SOR_QTY_COMPLETED,
SOR_QTY_COMPLETED_PREVIOUS,
SOR_VAR_REASON,
APPLYMAP('MAP_CODE_DESC','IVAR'&SOR_VAR_REASON,NULL()) AS SOR_VAR_REASON_DESC,
SOR_WORK_LOCATION AS SOR_NOTES,
SOR_COMPLETION_DATE,
FLOOR(SOR_COMPLETION_DATE) AS SOR_COMPLETION_DATE_DATE,
SK_JOBKEY&'|'&JOB_ITEM_NO AS SK_QV_FJR_SOR,
SK_JOBKEY&'|'&JOB_ITEM_NO&'|'&JOB_ITEM_STATUS AS SK_QV_FJRS_SOR,
SK_JOBKEY,
//SK_APPLIANCEKEY,
JOB_ITEM_NO,
FORNM_SOURCE,
USER_NAME,
SCHEDULE_OF_RATES_QTY,
JOB_ITEM_VALUE,
JOB_ITEM_STATUS,
APPLYMAP('MAP_CODE_DESC','WSTA'&JOB_ITEM_STATUS,'Unknown') AS JOB_ITEM_STATUS_DESC,
TOTAL_STANDARD_MINUTES,
IF(TOTAL_STANDARD_MINUTES>480,1,0) AS SOR_ONEDAYPLUS_FLAG,
//CONTRACT_NO,
TRADE_CODE,
SOR_CODE,
SOR_DESCRIPTION,
//APPLYMAP('MAP_SOR_CODE_CONCAT',SOR_CODE&'|'&SOR_DESCRIPTION) AS SOR_CODE_AND_DESCRIPTION_CONCAT,
APPLYMAP('MAP_SOR_CODE_CONCAT',SK_JOBKEY,null()) AS SOR_ITEMNO_LIST,
STANDARD_MINUTES_VALUE,
SOR_EMPLOYEE_CODE,
IF(LEFT(SOR_CODE,6)=460012 AND MATCH(JOB_ITEM_STATUS,'0','1','2','3','8'),'1',
IF(LEFT(SOR_CODE,4)=4600 AND NOT LEFT(SOR_CODE,6)=460012 AND MATCH(JOB_ITEM_STATUS,'0','1','2','3','8'),'2',
IF(LEFT(SOR_CODE,6)=896001 AND MATCH(JOB_ITEM_STATUS,'0','1','2','3','8'),'3','N/A'
))) AS SOR_VOID_MAP,
'Place Holder' AS SOR_DISCOUNT,
CAPITALIZE(APPLYMAP('MAP_EMPLOYEE_NAME',SOR_EMPLOYEE_CODE,NULL())) AS SOR_EMPLOYEE_NAME


FROM
[$(vQVDDir)FACT_SOR$(vEnvFileExt).qvd]
(qvd);

The SK_JOBKEY replaces JOB_ID and the SOR_QTY_ORDERED replaces SOR_QTY. Can you advise where exactly i need to place the solution :-

Load SK_JOBKEY, Concat(JOB_ITEM_NO&','& SOR_CODE&','& SOR DESCRIPTION &','&SOR_QTY_ORDERED, ';') as SOR_DETAiLS_CONCAT

resident ????????  group by SK_JOBKEY;

Thanks

[MayilVahanan · Accepted solution]

Hi @RedDead 

After loaded the qvd, try like below

Load SK_JOBKEY, Concat(JOB_ITEM_NO&','& SOR_CODE&','& SOR DESCRIPTION &','&SOR_QTY_ORDERED, ';') as SOR_DETAiLS_CONCAT

resident SoR  group by SK_JOBKEY;

FYI: above script create new table and join with SoR facttable with SK_JOBKEY.

If required, you can remove fields from SoR table after new logic.

[RedDead]

Hi,

This has worked great and exactly what i need. Thanks very much for your help

Alex