Work with data in a single field, separated by comma

[Anonymous]

Good day,

there are 2 tables

1) the list of services

id_service; - service code

name_service; - name of the service

2) the journal of services rendered

id; - code recording

id_service; - service code

The problem is that the developer stores data about the list of services separated by commas in a single field

Example:

id; id_service

1; [1]

2; [1,2,3,4]

3; [2,1,2]

Questions

1) how can you count the number of services?

2) how you can substitute the name of the service?

Thank you for your attention

[#2]

Hi,

You can use the subfield function to split id_service across multiple records. See the help file for a description of the function.

After that you can either use applymap or join to replace the id_service with a name.

Kind regards,

Matthijs

[Gysbert_Wassenaar · Accepted solution]

use the subfield function to split the comma separated list into values:

LOAD id, subfield(purgechar(id_service,'[]')) as id_service

FROM ...

You can then count the number of services per id with count(id_service).

You can then use a mapping table to substitute the service name for the id.

mapService:

MAPPING LOAD id_service, name_service

FROM ...

LOAD id, applyfield('mapService',id_service) as name_service;

LOAD id, subfield(purgechar(id_service,'[]')) as id_service

FROM ...

[sunny_talwar]

Do you have a sample data? It would be much easier to show you how it works then to put it in words.

Best,

Sunny

[Anonymous]

Hi, thank for the prompt

You really helped