SPSSX Discussion

spss.BasePivotTable

Classic

List

Threaded

8 messages Options

Albert-Jan Roskam

spss.BasePivotTable

Hello,

I am trying to create a BasePivot table with three columns: one for the labels, one for a a category "Before" and one for "After". The following attempt fails, but what am I doing wrong?

begin program.
import spss
try:
spss.StartSPSS()
#spss.Submit("get file='demo.sav'.")
spss.StartProcedure("proc")
table = spss.BasePivotTable("table","mytable")
table.Append(spss.Dimension.Place.row,"rowdim")
table.Append(spss.Dimension.Place.column,"coldim1")
table.Append(spss.Dimension.Place.column,"coldim2")
value1 = spss.CellText.Number(23,spss.FormatSpec.Count)
value2 = spss.CellText.Number(24,spss.FormatSpec.Count)
table[(spss.CellText.String("M"),)] = (value1, value2)
spss.EndProcedure()
spss.StopSPSS()
except spss.SpssError:
print "Error."
print spss.GetLastErrorMessage()
end program.

Thank you in advance!

Regards,
Albert-Jan

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Albert-Jan Roskam

Re: spss.BasePivotTable

> Subject: spss.BasePivotTable
>
> Hello,
>
> I am trying to create a BasePivot table with three columns: one for the labels,
> one for a a category "Before" and one for "After". The
> following attempt fails, but what am I doing wrong?

never mind ;-)

begin program.
import spss
try:
spss.StartSPSS()
spss.StartProcedure("proc")
table = spss.BasePivotTable("table","mytable")
table.Append(spss.Dimension.Place.row,"rowdim")
table.Append(spss.Dimension.Place.column,"coldim")
asStr, asInt = spss.CellText.String, spss.CellText.Number
category1 = asStr("Blah")
category2 = asStr("Before")
category3 = asStr("After")
before_value, after_value = 23, 24
table[(category1,category2)] = asInt(before_value,spss.FormatSpec.Count)
table[(category1,category3)] = asInt(after_value,spss.FormatSpec.Count)
spss.EndProcedure()
except spss.SpssError:
print "Error."
print spss.GetLastErrorMessage()
end program.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Albert-Jan Roskam

Re: spss.BasePivotTable

> From: Albert-Jan Roskam <[hidden email]>
> To: SPSS Mailing List <[hidden email]>
> Cc:
> Sent: Tuesday, August 27, 2013 4:04 PM
> Subject: Re: spss.BasePivotTable
>
>> Subject: spss.BasePivotTable
>>
>> Hello,
>>
>> I am trying to create a BasePivot table with three columns: one for the
> labels,
>> one for a a category "Before" and one for "After". The
>> following attempt fails, but what am I doing wrong?
>
> never mind ;-)
>
> begin program.
> import spss
> try:
> spss.StartSPSS()
> spss.StartProcedure("proc")
> table = spss.BasePivotTable("table","mytable")
> table.Append(spss.Dimension.Place.row,"rowdim")
> table.Append(spss.Dimension.Place.column,"coldim")
> asStr, asInt = spss.CellText.String, spss.CellText.Number
> category1 = asStr("Blah")
> category2 = asStr("Before")
> category3 = asStr("After")
> before_value, after_value = 23, 24
> table[(category1,category2)] = asInt(before_value,spss.FormatSpec.Count)
> table[(category1,category3)] = asInt(after_value,spss.FormatSpec.Count)
> spss.EndProcedure()
> except spss.SpssError:
> print "Error."
> print spss.GetLastErrorMessage()
> end program.
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Jon K Peck

Re: spss.BasePivotTable

In older versions, there were sometimes timing problems where a command like MODIFY TABLES would fail to find an immediately preceding table, because the Viewer had not finished instantiating it. The Viewer runs many threads concurrently, and occasionally they could get out of sync. You can test this and get around the problem by putting a time.sleep call in with .5 or 1 second delay before the MODIFY TABLES call.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Albert-Jan Roskam <[hidden email]>
To: [hidden email],
Date: 08/27/2013 09:36 AM
Subject: Re: [SPSSX-L] spss.BasePivotTable
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Still one question though (Jon, probably): I successfully created a custom pivot table. As a last step, I want to color certain cells red using SPSSINC MODIFY TABLES. The Modify Tables command works if I run it separately, but if I run the BasePivotTable code + Modify Tables code in one go, nothing happens (the cells are not colored, no error). It's like the generation of the custom pivot table is not finished yet and Modify Tables "concludes" there is no output of that OMS subtype in the output. Is there a way to correct/synchronize this? Regards, Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ----- Original Message ----- > From: Albert-Jan Roskam <[hidden email]> > To: SPSS Mailing List <[hidden email]> > Cc: > Sent: Tuesday, August 27, 2013 4:04 PM > Subject: Re: spss.BasePivotTable > >> Subject: spss.BasePivotTable >> >> Hello, >> >> I am trying to create a BasePivot table with three columns: one for the > labels, >> one for a a category "Before" and one for "After". The >> following attempt fails, but what am I doing wrong? > > never mind ;-) > > begin program. > import spss > try: > spss.StartSPSS() > spss.StartProcedure("proc") > table = spss.BasePivotTable("table","mytable") > table.Append(spss.Dimension.Place.row,"rowdim") > table.Append(spss.Dimension.Place.column,"coldim") > asStr, asInt = spss.CellText.String, spss.CellText.Number > category1 = asStr("Blah") > category2 = asStr("Before") > category3 = asStr("After") > before_value, after_value = 23, 24 > table[(category1,category2)] = asInt(before_value,spss.FormatSpec.Count) > table[(category1,category3)] = asInt(after_value,spss.FormatSpec.Count) > spss.EndProcedure() > except spss.SpssError: > print "Error." > print spss.GetLastErrorMessage() > end program. > ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

Jon K Peck

Re: spss.BasePivotTable

In reply to this post by Albert-Jan Roskam

Also, I should have mentioned that you can run the command
_SYNC
to pause backend execution until the Viewer is ready.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Albert-Jan Roskam <[hidden email]>
To: [hidden email],
Date: 08/27/2013 09:36 AM
Subject: Re: [SPSSX-L] spss.BasePivotTable
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Albert-Jan Roskam

Re: spss.BasePivotTable

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: Jon K Peck <[hidden email]>
To: [hidden email]
Sent: Tuesday, August 27, 2013 6:23 PM
Subject: Re: [SPSSX-L] spss.BasePivotTable

Also, I should have mentioned that you can run the command
_SYNC
to pause backend execution until the Viewer is ready.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Albert-Jan Roskam <[hidden email]>
To: [hidden email],
Date: 08/27/2013 09:36 AM
Subject: Re: [SPSSX-L] spss.BasePivotTable
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Still one question though (Jon, probably): I successfully created a custom pivot table. As a last step, I want to color certain cells red using SPSSINC MODIFY TABLES. The Modify Tables command works if I run it separately, but if I run the BasePivotTable code + Modify Tables code in one go, nothing happens (the cells are not colored, no error). It's like the generation of the custom pivot table is not finished yet and Modify Tables "concludes" there is no output of that OMS subtype in the output. Is there a way to correct/synchronize this? Regards, Albert-Jan ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh water system, and public health, what have the Romans ever done for us? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ----- Original Message ----- > From: Albert-Jan Roskam <[hidden email]> > To: SPSS Mailing List <[hidden email]> > Cc: > Sent: Tuesday, August 27, 2013 4:04 PM > Subject: Re: spss.BasePivotTable > >> Subject: spss.BasePivotTable >> >> Hello, >> >> I am trying to create a BasePivot table with three columns: one for the > labels, >> one for a a category "Before" and one for "After". The >> following attempt fails, but what am I doing wrong? > > never mind ;-) > > begin program. > import spss > try: > spss.StartSPSS() > spss.StartProcedure("proc") > table = spss.BasePivotTable("table","mytable") > table.Append(spss.Dimension.Place.row,"rowdim") > table.Append(spss.Dimension.Place.column,"coldim") > asStr, asInt = spss.CellText.String, spss.CellText.Number > category1 = asStr("Blah") > category2 = asStr("Before") > category3 = asStr("After") > before_value, after_value = 23, 24 > table[(category1,category2)] = asInt(before_value,spss.FormatSpec.Count) > table[(category1,category3)] = asInt(after_value,spss.FormatSpec.Count) > spss.EndProcedure() > except spss.SpssError: > print "Error." > print spss.GetLastErrorMessage() > end program. > ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

Jon K Peck

Re: spss.BasePivotTable

_SYNC is a traditional-style syntax command.
Commands that start with _ are not echoed to the Viewer and are generally very specialized and not included in the CSR.

I don't think that upgrading MODIFY TABLES will help, although upgrading Statistics might. However, with a 2 second delay not working I wonder whether your problem is different. Are you sure that you have the table subtype right?

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Albert-Jan Roskam <[hidden email]>
To: Jon K Peck/Chicago/IBM@IBMUS, "[hidden email]" <[hidden email]>,
Date: 08/27/2013 10:58 AM
Subject: Re: [SPSSX-L] spss.BasePivotTable

Hi Jon,

Great, I did not know about the _SYNC command. Is that legacy syntax (because you wrote it in caps)? I do not know any other syntax command that starts with an underscore. I actually tried time.sleep(2) already today, but that did not work. The third option you seem to suggest is to upgrade SPSSINC MODIFY TABLES. I will do that too.

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: Jon K Peck <[hidden email]>
To: [hidden email]
Sent: Tuesday, August 27, 2013 6:23 PM
Subject: Re: [SPSSX-L] spss.BasePivotTable

Also, I should have mentioned that you can run the command
_SYNC
to pause backend execution until the Viewer is ready.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Albert-Jan Roskam <[hidden email]>
To: [hidden email],
Date: 08/27/2013 09:36 AM
Subject: Re: [SPSSX-L] spss.BasePivotTable
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Still one question though (Jon, probably): I successfully created a custom pivot table. As a last step, I want to color certain cells red using SPSSINC MODIFY TABLES. The Modify Tables command works if I run it separately, but if I run the BasePivotTable code + Modify Tables code in one go, nothing happens (the cells are not colored, no error). It's like the generation of the custom pivot table is not finished yet and Modify Tables "concludes" there is no output of that OMS subtype in the output. Is there a way to correct/synchronize this?

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----
> From: Albert-Jan Roskam <[hidden email]>
> To: SPSS Mailing List <[hidden email]>
> Cc:
> Sent: Tuesday, August 27, 2013 4:04 PM
> Subject: Re: spss.BasePivotTable
>
>> Subject: spss.BasePivotTable
>>
>> Hello,
>>
>> I am trying to create a BasePivot table with three columns: one for the
> labels,
>> one for a a category "Before" and one for "After". The
>> following attempt fails, but what am I doing wrong?
>
> never mind ;-)
>
> begin program.
> import spss
> try:
> spss.StartSPSS()
> spss.StartProcedure("proc")
> table = spss.BasePivotTable("table","mytable")
> table.Append(spss.Dimension.Place.row,"rowdim")
> table.Append(spss.Dimension.Place.column,"coldim")
> asStr, asInt = spss.CellText.String, spss.CellText.Number
> category1 = asStr("Blah")
> category2 = asStr("Before")
> category3 = asStr("After")
> before_value, after_value = 23, 24
> table[(category1,category2)] = asInt(before_value,spss.FormatSpec.Count)
> table[(category1,category3)] = asInt(after_value,spss.FormatSpec.Count)
> spss.EndProcedure()
> except spss.SpssError:
> print "Error."
> print spss.GetLastErrorMessage()
> end program.
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Albert-Jan Roskam

Re: spss.BasePivotTable

Hi Jon,

I copied the OMS subtype by right-clicking on the table so I don't think anything could have gone wrong here ("mytable" in the example I posted). After creating this table I also check if the before/after discrepancies exceed a certain tolerance, and if so, I issue one or more custom warnings. But outcommenting that part of the code did not matter. Perhaps it is because our VMs have been rather slow lately. A while ago we also occasionally experiencied what seemed to be synchronization problems with VB6 and SPSS.

Regards,
Albert-Jan

From: Jon K Peck <[hidden email]>
To: Albert-Jan Roskam <[hidden email]>
Cc: "[hidden email]" <[hidden email]>
Sent: Tuesday, August 27, 2013 7:09 PM
Subject: Re: [SPSSX-L] spss.BasePivotTable

_SYNC is a traditional-style syntax command.
Commands that start with _ are not echoed to the Viewer and are generally very specialized and not included in the CSR.

I don't think that upgrading MODIFY TABLES will help, although upgrading Statistics might. However, with a 2 second delay not working I wonder whether your problem is different. Are you sure that you have the table subtype right?

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Albert-Jan Roskam <[hidden email]>
To: Jon K Peck/Chicago/IBM@IBMUS, "[hidden email]" <[hidden email]>,
Date: 08/27/2013 10:58 AM
Subject: Re: [SPSSX-L] spss.BasePivotTable

Hi Jon,

Great, I did not know about the _SYNC command. Is that legacy syntax (because you wrote it in caps)? I do not know any other syntax command that starts with an underscore. I actually tried time.sleep(2) already today, but that did not work. The third option you seem to suggest is to upgrade SPSSINC MODIFY TABLES. I will do that too.

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: Jon K Peck <[hidden email]>
To: [hidden email]
Sent: Tuesday, August 27, 2013 6:23 PM
Subject: Re: [SPSSX-L] spss.BasePivotTable

Also, I should have mentioned that you can run the command
_SYNC
to pause backend execution until the Viewer is ready.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

From: Albert-Jan Roskam <[hidden email]>
To: [hidden email],
Date: 08/27/2013 09:36 AM
Subject: Re: [SPSSX-L] spss.BasePivotTable
Sent by: "SPSSX(r) Discussion" <[hidden email]>

Still one question though (Jon, probably): I successfully created a custom pivot table. As a last step, I want to color certain cells red using SPSSINC MODIFY TABLES. The Modify Tables command works if I run it separately, but if I run the BasePivotTable code + Modify Tables code in one go, nothing happens (the cells are not colored, no error). It's like the generation of the custom pivot table is not finished yet and Modify Tables "concludes" there is no output of that OMS subtype in the output. Is there a way to correct/synchronize this?

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----
> From: Albert-Jan Roskam <[hidden email]>
> To: SPSS Mailing List <[hidden email]>
> Cc:
> Sent: Tuesday, August 27, 2013 4:04 PM
> Subject: Re: spss.BasePivotTable
>
>> Subject: spss.BasePivotTable
>>
>> Hello,
>>
>> I am trying to create a BasePivot table with three columns: one for the
> labels,
>> one for a a category "Before" and one for "After". The
>> following attempt fails, but what am I doing wrong?
>
> never mind ;-)
>
> begin program.
> import spss
> try:
> spss.StartSPSS()
> spss.StartProcedure("proc")
> table = spss.BasePivotTable("table","mytable")
> table.Append(spss.Dimension.Place.row,"rowdim")
> table.Append(spss.Dimension.Place.column,"coldim")
> asStr, asInt = spss.CellText.String, spss.CellText.Number
> category1 = asStr("Blah")
> category2 = asStr("Before")
> category3 = asStr("After")
> before_value, after_value = 23, 24
> table[(category1,category2)] = asInt(before_value,spss.FormatSpec.Count)
> table[(category1,category3)] = asInt(after_value,spss.FormatSpec.Count)
> spss.EndProcedure()
> except spss.SpssError:
> print "Error."
> print spss.GetLastErrorMessage()
> end program.
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD