Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
serg_ysn
Contributor III
Contributor III

Work with data in a single field, separated by comma

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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 ...


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Not applicable

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

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 ...


talk is cheap, supply exceeds demand

View solution in original post

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

serg_ysn
Contributor III
Contributor III
Author

Hi, thank for the prompt

You really helped