Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
RedDead
Contributor II
Contributor II

Concat Multiple Fields into One

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

 

Capture.PNG

 

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

 

Thanks

 

Alex

1 Solution

Accepted Solutions
MayilVahanan

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

4 Replies
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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
RedDead
Contributor II
Contributor II
Author

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

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.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
RedDead
Contributor II
Contributor II
Author

Hi,

 

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

 

Alex