topic Re: convert rows to columns in script in QlikView

convert rows to columns in script

Margaret — Wed, 25 Nov 2020 16:16:04 GMT

I want to count up the times that physicians of different specialties opened their e-Newsletter in particular months. I want to do this in the script so that I can later calculate percentages for each month and total percentage.

Here's my source data:

Specialty Open

Cardiology August

Obstetrics August

Ear Nose Throat August

Cardiology August

Obstetrics July

Cardiology September

Here is my desired result set:

Specialty August July September

Cardiology 2 - -

Obstetrics 1 1 -

Ear Nose Throat 1 - 1

How can I do that in Script

Thank you!!

Re: convert rows to columns in script

Chanty4u — Fri, 01 Apr 2016 07:03:38 GMT

create stright or pivot table

dimension:Specality

Exp:count(open)

Re: convert rows to columns in script

Kushal_Chawda — Fri, 01 Apr 2016 07:07:36 GMT

Create the Pivot table:

Dimension :

Specialty

Month

Expression:

Count (Specialty)

Now drag the month on expression column

Re: convert rows to columns in script

tamilarasu — Fri, 01 Apr 2016 07:10:33 GMT

Hi Margaret,

Check the attachment

Re: convert rows to columns in script

settu_periasamy — Fri, 01 Apr 2016 07:29:19 GMT

Hi,

If you want that in Script level try with Generic Load..

Use cases for Generic Load | Qlikview Cookbook

t1:
LOAD * INLINE [
Specialty, Open
Cardiology, August
Obstetrics, August
Ear Nose Throat, August
Cardiology, August
Obstetrics, July
Cardiology, September
];

T1_New:
LOAD Distinct Specialty Resident t1;

temp:
generic LOAD Specialty,Open,Count(Open) as Count Resident t1 Group by Specialty,Open;

DROP Table t1;

FOR i = NoOfTables()-1 to 0 STEP -1
LET vTable=TableName($(i));
IF WildMatch('$(vTable)', 'temp*') THEN
LEFT JOIN (T1_New) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
ENDIF
NEXT i