Split-up cells (Subfield)

[Werner95]

Good day Qlickers. Hope someone can help me.

My datasource is Smartsheets. The users fill in reason/s why a job is late. Problem is they sometimes fill in more than 1 reason per cell.

I need to split-up those cells into the seperate reason? How do I do that?

See here:

Other issue is that the number of reasons do differ. There can be 1 of there can be 10 reason. 

The separtor in Smartsheets is a comma.

I need to have COURIER, MISSING SAMPLES, RESOURCES_GIS, MAPS QC etc. To be in seperate lines. I hope this is possible and that someone can help me.

Kind regards

[anat · Accepted solution]

can you try using subfield function like:

Source_Data:

Load * Inline [

Group_Id,Items

1,"Bread,Biscuit,Chocolate"

2,"Jean,Shirt,Shoes"

;

NoConcatenate

LOAD

Group_Id,

SubField(Items,',') as Items

Resident Source_Data;

Drop Table Source_Data;

[Werner95]

Hi @anat 
See my Script here:

Summary:
LOAD
mmCal_det,
country_det,
serviceCode_det,
hc_det,
key,
serviceName_det,
service_det,
hcOrigField_det,
spreadSheet_det,
workSheet_det,
date(date#(dateEntry_det, 'YYYY/MM/DD')) as dateEntry_det,
dateEntryOrigValue_det,
dateEntryOrigField_det,
dateCompleted_det,
dateCompletedOrigValue_det,
dateCompletedOrigField_det,
Upper(personAllocatedTest_det) as personAllocatedTest_det,
Upper(personAllocatedTestOrigField_det) as personAllocatedTestOrigField_det,
Upper(personAllocatedQC_det) as personAllocatedQC_det,
test1_det,
test2_det,
test3_det,
flagResultsReceived_det,
flagReportComplete_det,
flagPdfReceived_det,
flagMfwTotalDataPointsLoaded_det,
flagMfwTotalLoadedAllMFW_det,
Upper(agriculturist_det) as agriculturist_det,
Upper(depot_det) as depot_det,
Upper(crop_det) as crop_det,
cropCat_det,
Upper(entityName_det) as entityName_det,
Upper(clientName_det) as clientName_det,
Upper(farmName_det) as farmName_det,
Upper(area_det) as area_det,
qtySamples_det,
qtySamplesOrigField_det,
ftaAssigned_det,
actualFWdateflag_det,
qtyLines_det,
poNr_det,
sample_det,
dateResultsReceived_det,
dateReportComplete_det,
OrderStatus_det,
dateCompleted2_det,
dateCompletedOrigValue2_det,
dateCompletedOrigField2_det,
dateCompletedGIS_det,
dateCompletedGISOrigField_det,
Upper(personAllocatedGIS_det) as personAllocatedGIS_det,
Upper(personAllocatedGISOrigField_det) as personAllocatedGISOrigField_det,
qtyHaGIS_det,
qtyHaGISOrigField_det,
qtyFWHa_det,
qtyFWHaOrigField_det,
qtyFWAc_det,
qtyFWAcOrigField_det,
qtyHa_det,
qtyAcre_det,

fwDateEnd_det,

      upper(reasonlate_det) as reasonlate_det,

dateResultsRecieved_det,
actualFWdateflag2_det,
qtyHoles_det,
qtyHolesOrigField_det,
qtyGPTubes_det,
qtyGPTubesOrigField_det,
qtyAcGIS_det,
qtyAcGISOrigField_det,
dateMeasurementsRecieved_det,
dateMeasurementsRecieveddOrigValue_det,
dateMeasurementsRecieveddOrigField_det,
dateQCCompleted_det,
dateQCCompletedOrigValue_det,
dateQCCompletedOrigField_det,
Upper(personFSSResponsable_det) as personFSSResponsable_det,
personFSSResponsableOrigField_det,
NIDpoints_det,
NIDpointsOrigField_det,
FSSPoints_det,
FSSPointsOrigField_det,
samplesTakenBy_det,
samplesTakenByOrigField_det,
qtyLinesOrigField_det,
pricecategory_det,
qtyHaOrigField_det,
ppmStatus_det,
ppmStatusOrigField_det,
qtyHaPO_det,
qtyHaPOOrigField_det,
ppmItemName_det,
ppmItemNameOrigField_det,
// fwStatus_det,
Durationtocompleteminutes_det,
Relatedservice_det,
ActualHarvestHectares_det,
WorkingDays_det,
// CountLate_det,
Despcription_det,
JobStatus_det,
TypeofRequest_det,
TargetCompletionDate_det,
RequestReceivedFrom_det,
durationDaysWorkMax_det,
zRef_HCDuplicate_det,
durationDaysWorkMax2_det,
flagPONr_det,
flagGIS_det,
flagCompleted_det,
flagCompleted2_det,
flagCompletedGIS_det,
zError_HCEmpty_det,
zError_HCDuplicate_det,
zError_dateEntryEmpty_det,
zError_dateEntryFormat_det,
zError_dateCompleted_det,
zError_durationDaysWorkMax_det,
durationDaysWork_det,
durationDaysWork2_det,
durationDaysCalendar_det,
durationDaysCalendar2_det,
yyyyCal_det,
mmmCal_det,
yyyyFiscal_det,
flagLate_det,
flagLate2_det,
dateExpected_det,
dateExpected2_det,
dateToday_det,
ppFiscal_det,
pppFiscal_det,
ppMMMFiscal_det,
flagAlreadyLate_det,
flagAlreadyLate2_det,
yyyyPPMMMFiscal_det,
yyyyMMMCal_det,
yyyyMMCal_det,
flagExcludeError_det,
TYPEOFANALYSIS_det,
rowlink_det,

//****SOIL SCIENTIST EVALUATE****//
soilScientistResp_det,
dateDigitizingSent_det,
dateActualQCDate_det,
dateCreated_det,

//****INVOICING****//
INVOICETOTAL_det,

//****LATE ANALYSIS & LOGISTICS REPORT (2021/08/02)****//
fields_det,
date(date#(actualFWdate_det, 'YYYY/MM/DD')) as actualFWdate_det,
date(date#(dateSamplessent_det, 'YYYY/MM/DD')) as dateSamplessent_det,
date(date#(dateSamplesDeliveredToLab_det, 'YYYY/MM/DD')) as dateSamplesDeliveredToLab_det,
NetWorkDays(dateSamplessent_det,dateSamplesDeliveredToLab_det) as logisticsdays,
bookname_det,
date(date#(dateBookCompleted_det, 'YYYY/MM/DD')) as dateBookCompleted_det,
date(date#(datePDFEmailed_det, 'YYYY/MM/DD')) as datePDFEmailed_det,
date(date#(datePDFUploaded_det, 'YYYY/MM/DD')) as datePDFUploaded_det,


country_det&'|'&serviceName_det as %Key_Summary_Projection,

//****AGRICULTURIST REPORT****//
dateReportSSent_det,
dateReport@Customer_det,
dateActualFieldwork_det,
report@customerflag_det,

//****MRC LATE REPORT****//
dateSamplesDeliveredLab_det,
dateResultsRecPub_det,
dateCompleted05_det,

ApplyMap('CROP',Upper(crop_det),'##MAPPING NOT FOUND') AS CROP_CLEAN,
ApplyMap('AGRICULTURIST',Upper(agriculturist_det),'##MAPPING NOT FOUND') AS AGRICULTURIST_CLEAN,
ApplyMap('PERSONALLOCATEDTOTEST',Upper(personAllocatedTest_det),'##MAPPING NOT FOUND') AS PERSONTOTEST_CLEAN,
ApplyMap('PERSONALLOCATEDTOTESTGIS',Upper(personAllocatedGIS_det),'##MAPPING NOT FOUND') AS PERSONTOTESTGIS_CLEAN,
ApplyMap('DEPOT',Upper(depot_det),'##MAPPING NOT FOUND') AS DEPOT_CLEAN,

// actualFWdate_det,

'SMARTSHEETS' AS Source_Summary

//**LIVE Qlik Sense qvd's**//
FROM [lib://SMARTSHEET_STEP2 (bij001_truserv__administrator)/2_ops_detail.qvd] (qvd);

Attached is a list of all the reasons late. See here that the amount of reasons people give per cell/line in Smartsheets differ.

Please advise on code I should use.

Kind regards

[chris_djih · Accepted solution]

Hello Werner,
the solution form Anat is the way to go. I'll try to exlain it more. But be aware that nobody has time to go through this massive script and try to understand it. So i hope that you can get the key point out of my explaination and can modify your script yourself.

How do ou identify each line of data? Which field/column is the unique identifier. As placeholder I will use "Identifier" :

Identifier	all the other columns...	reasonlate_det
100001	....	COURIER
100002	...	COURIER, LAB, REWORK
100003	...	COURIER, MISSING SAMPLES

So first task: find the Identifier.
Now you have to load a new table:

Reasons:
Load
  Identifier, //please replace with the correct field name
  Subfield(reasonlate_detm,',') as Reason
resident Summary;

 This will generate a table in which for each Identifier all Reasons are listet as seperate Values.