database set up

[#1]

Hello

I have the following excel table

#	Country	Hospital name	Switch on HLM Satisfaction	Calibrate venous occluder Satisfaction	Switch on level Satisfaction	Recirculate CPL Satisfaction	Switch on HLM times per case	Calibrate venous occluder times per case	Switch on level times per case	Recirculate CPL times per case
1	DE	Giessen, pediatric	3	3	1	3	1	1	1	1
2	DE	Karlsruhe	1		2	3	1		1	1
3	UK	GOSH, London, pediatric	3		1	3	1		1	1

I would like to re-organize it (through the script) like the following:

#	Country	Hospital name	Features	Satisfaction rates	Times per case
1	DE	Giessen, pediatric	Switch on HLM	3	1
1	DE	Giessen, pediatric	Calibrate venous occluder	3	1
1	DE	Giessen, pediatric	Switch on level	1	1
1	DE	Giessen, pediatric	Recirculate CPL	3	1
2	DE	Karlsruhe	Switch on HLM	1	1
2	DE	Karlsruhe	Calibrate venous occluder
2	DE	Karlsruhe	Switch on level	2	1
2	DE	Karlsruhe	Recirculate CPL	3	1
3	UK	GOSH, London, pediatric	Switch on HLM	3	1
3	UK	GOSH, London, pediatric	Calibrate venous occluder
3	UK	GOSH, London, pediatric	Switch on level	1	1
3	UK	GOSH, London, pediatric	Recirculate CPL	3	1

please note that names contained into the "Features" column, are a bit different compared to the first table showed above. I would like them to be without "satisfaction" or "times per case" portion of the name.

How should I do? do I need to create a cross table? how should I do it?

I am pretty new with qlik sense

thank you for your help

Jessica

[marcus_malinow]

Hi Jessica,

this can be achieved using a Crosstable:

CrossTable(Features, [Satisfaction Rates], 3)
LOAD #,
Country,
[Hospital name],
[Switch on HLM Satisfaction],
[Calibrate venous occluder Satisfaction],
[Switch on level Satisfaction],
[Recirculate CPL Satisfaction],
[Switch on HLM times per case],
[Calibrate venous occluder times per case],
[Switch on level times per case],
[Recirculate CPL times per case]
FROM
[https://community.qlik.com/thread/257387]
(html, codepage is 1252, embedded labels, table is @1
);

[#3]

thank you for your support

unfortunately the script you suggested doesn't fix my problem.

Now I am trying with joint tables, but no solutions found yet.

thank you

[Anil_Babu_Samineni]

Can you share this data in Excel please

[marcus_malinow]

ok, what about this:

tmp:
CrossTable(Features, [Satisfaction Rates], 3)
LOAD #,
Country,
[Hospital name],
[Switch on HLM Satisfaction],
[Calibrate venous occluder Satisfaction],
[Switch on level Satisfaction],
[Recirculate CPL Satisfaction]

FROM
[https://community.qlik.com/thread/257387]
(html, codepage is 1252, embedded labels, table is @1
);

Data:
NOCONCATENATE
Load #,
Country,
[Hospital name],
Left(Features, index(Features, ' Satisfaction') - 1) as Features,
[Satisfaction Rates]
RESIDENT tmp;

DROP TABLE tmp;


tmp:
CrossTable(Features, [Times Per Case], 3)
LOAD #,
Country,
[Hospital name],
[Switch on HLM times per case],
[Calibrate venous occluder times per case],
[Switch on level times per case],
[Recirculate CPL times per case]
FROM
[https://community.qlik.com/thread/257387]
(html, codepage is 1252, embedded labels, table is @1
);


LEFT JOIN (Data)
Load #,
Country,
[Hospital name],
Left(Features, index(Features, ' times per case') - 1) as Features,
[Times Per Case]
RESIDENT tmp
;

DROP TABLE tmp;

[#6]

Great!!!

it works...

thank you

[marcus_malinow]

Glad to hear it - could you mark the answer correct?

Thanks