topic Re: database set up in QlikView

database set up

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: database set up

marcus_malinow — Tue, 18 Apr 2017 12:09:29 GMT

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
);

Re: database set up

Thu, 20 Apr 2017 10:43:24 GMT

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

Re: database set up

Anil_Babu_Samineni — Thu, 20 Apr 2017 10:48:35 GMT

Can you share this data in Excel please

Re: database set up

marcus_malinow — Thu, 20 Apr 2017 11:04:05 GMT

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;

Re: database set up

Thu, 20 Apr 2017 16:09:13 GMT

Great!!!

it works...

thank you

Re: database set up

marcus_malinow — Fri, 21 Apr 2017 09:05:57 GMT

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

Thanks