Transforming data fields within Qlik Sense

[JavierBlanco]

Hi everybody,

My client wants to visualize data in a way, given the data source they're also providing, I think it's pretty complicated to; let me explain the issue with an example:

I'm receiving data in CSV files, tables like this:

store_id, target_serv1, target_serv2, ..., target_servN, achieve_serv1,  achieve_serv2, ..., achieve_servN

But they want me to represent it this way:

                      Target   Achievement        

Service 1   

Service 2

...

Service n

I think it's not possible to do this without transforming the data within Qlik, I guess using its script language. It's my first project with Qlik and, besides that, I'm used to work with another BI software but always with MySQL sources, so I decide how to import data using SQL, so I would need some starting hints: do I need to alter the loading script, creating new fields, or maybe in a later stage, via variables... ?

Any help would be greatly appreciated, thanks in advance. 

[marcus_sommer]

I think you could use The-Crosstable-Load .

- Marcus

[JavierBlanco]

I'm going to try, but I'd need to somehow split field names into two pieces, one with the name of the service, which would be placed in the Y axis, and another with the name of the measurable parameter (target, achievement, etc.), which would be place in the X axis... Would it be possible to achieve this with just a crosstable?

Thanks a lot for your answer, Marcus.

[marcus_sommer]

I think you could use one crosstable-load and split the category-field into two fields in a following step which you could later use as dimensions and/or set analysis-conditions in the expressions to build the wanted views.

As alternatively you could just split the task into crosstable-loads - one with the target- and one with the achievement-fields and join/map them then together again.

- Marcus

[JavierBlanco]

I think the first solution suits me better, cause the real table is more complex than the portrayed example; I guess Qlik script language has some function to split strings, if that is what you were referring to.

[marcus_sommer]

There are a lot of string-functions and beside the classical ones, like: mid/left/right/index you may find pick/match/subfield/keepchar/purgechar particular useful.

- Marcus

[JavierBlanco]

I got stuck after setting up the crosstable (no problem with that).

I tried to create two new parameters, one dimension called "service" and one measure called "parameter"; I'm pretty sure this doesn't make sense but for the moment I haven't figured out something better.

First I created a variable called "index":

=FindOneOf(parameter_service '_', 1)

Then the dimension, "service":

=Mid(parameter_service, index)

And finally the measure, "parameter":

=PurgeChar(parameter_service, Mid(parameter_service, index))

Couldn't fit service into parameter, so I repeated its syntax.

Qlik says all of them are OK, but when I try to visualize them within a table, no outputs...

[marcus_sommer]

I assume I would try something like this:

t0: crosstable(category, value, 1) load * inline [
store_id, target_serv1, target_serv2, target_servN, achieve_serv1, achieve_serv2, achieve_servN
a, 10, 20, 50, 11, 19, 50
b, 10, 20, 50, 12, 22, 53
];

t1: load store_id, subfield(category, '_', 1) as type, subfield(category, '_', 2) as category, value
resident t0;
drop tables t0;

- Marcus

[JavierBlanco]

Great, Marcus, superb solution.

I get this (it's based on a test Excel file, I hope there's no trouble with Spanish):

 Is it possible to change the layout in order to display both parameters on top so services only appear once each of them?

              param1   param2            

serv1       40              38

serv2       19              20

serv3       35              31

[marcus_sommer · Accepted solution]

By using a pivot-chart you could add the parameter-field as a horizontal dimension. Another way would be just to use two expressions like:

sum({< parametro = {'consecution'}>} valor)
sum({< parametro = {'objetivo'}>} valor)

- Marcus