Split-up cells (Subfield)

Werner95 — Mon, 10 Jan 2022 10:11:57 GMT

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

Re: Split-up cells (Subfield)

anat — Mon, 10 Jan 2022 10:25:23 GMT

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;

Re: Split-up cells (Subfield)

Werner95 — Wed, 12 Jan 2022 06:44:40 GMT

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

Re: Split-up cells (Subfield)

chris_djih — Mon, 24 Jan 2022 13:06:25 GMT

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.

topic Split-up cells (Subfield) in App Development

Split-up cells (Subfield)

Re: Split-up cells (Subfield)

Re: Split-up cells (Subfield)

Re: Split-up cells (Subfield)