Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Werner95
Contributor
Contributor

Split-up cells (Subfield)

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:

Werner95_0-1641809283784.png

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

 

2 Solutions

Accepted Solutions
anat
Specialist II
Specialist II

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;

View solution in original post

chris_djih
Creator III
Creator III

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.

 

If you found help, mark the correct answer and give some likes to ALL contributors, that tried to help.

View solution in original post

3 Replies
anat
Specialist II
Specialist II

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
Contributor
Contributor
Author

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
Creator III
Creator III

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.

 

If you found help, mark the correct answer and give some likes to ALL contributors, that tried to help.