1. Reynard Harrison
  2. PowerBuilder
  3. Thursday, 8 June 2023 17:00 PM UTC

In a dynamic crosstab how can one find out the number of columns that where created?

Accepted Answer
Andreas Mykonios Accepted Answer Pending Moderation
  1. Friday, 9 June 2023 07:49 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi.

I will provide some code that does what John suggested but doesn't write to a file.

string ls_data, ls_line
integer li_pos, li_counter = 0

ls_data = dw_2.Object.DataWindow.Data

if ls_data <> "" then
	ls_line = left(ls_data, pos(ls_data, '~r~n'))
else
	return
end if

messagebox("Info:", ls_line)

li_pos = pos(ls_line, "~t")
if li_pos < 1 then return
do while li_pos > 0
	li_counter ++
	li_pos = pos(ls_line, "~t", li_pos + 1)
loop

// Attention: You have to increment count by 1 because you have tabs = columns count - 1...
messagebox("Columns:", li_counter + 1)

Same example implemented with pfc and using n_cst_string:

string ls_data, ls_parsed_data[], ls_parsed_line[]
n_cst_string lnv_str

ls_data = dw_2.Object.DataWindow.Data

if ls_data = "" then return

lnv_str.of_parsetoarray(ls_data, "~r~n", ls_parsed_data)

messagebox(string(upperbound(ls_parsed_data)), ls_data)

if upperbound(ls_parsed_data) > 0 then
	lnv_str.of_parsetoarray(ls_parsed_data[1], "~t", ls_parsed_line)
end if

messagebox(string(upperbound(ls_parsed_line)), ls_parsed_data[1])

Both example may need some adjustments as I did some basic testing.

Andreas.

Comment
  1. John Fauss
  2. Friday, 9 June 2023 13:44 PM UTC
Nicely done, Andreas! Your code helped me discover a very simple solution (see my follow-up post for details).
  1. Helpful 1
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 8 June 2023 19:18 PM UTC
  2. PowerBuilder
  3. # 1

Hi Reynard;

  AFAIK, there is no direct way to get the Column Count.

I would have expected CrossTabCount(n) to do that but it does not seem to work.  :-(

Regards ... Chris

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 8 June 2023 19:36 PM UTC
  2. PowerBuilder
  3. # 2

Hi, Reynard -

The following is a little out-of-the-box, but it might work for you (the described technique works, however):

1. Save the crosstab DW contents as a (temporary) CSV file without column headings: dw_1.SaveAs("C:\temp.csv",CSV!,False)

2. Open the CSV file for reading in line mode: li_filenum = FileOpen("C:temp.csv",LineMode!,Read!)

3. Read the first line: ll_numbytes = FileReadEx(li_filenum,ls_csv_line)

4. Count the number of commas and add one (assumes the line does not contain any quoted string value(s) that contain a comma). Code left as an exercise for the reader.

5. Close and delete the temp CSV file.

Best regards, John

Comment
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Friday, 9 June 2023 13:42 PM UTC
  2. PowerBuilder
  3. # 3

After looking at the code Andreas posted, it occurred to me that the solution can be obtained in a much simpler manner:

Any la_row

// Place the entire first row of data into a variable of type "Any".
la_row = dw_1.Object.Data[1]

// The "Any" variable now contains an array of Any's.
MessageBox("Crosstab Columns","There are "+String(UpperBound(la_row))+" columns.")

Please note that if the detail band contains any computed field DWObjects (such as a crosstabsum), those DWObject(s) will not be included in the results because they are not data columns.

John

Comment
  1. Andreas Mykonios
  2. Friday, 9 June 2023 15:40 PM UTC
la_row = dw_1.Object.Data[1]

I tried that but didn't worked for me with crosstabs. :-(

I will try it again.

Andreas.
  1. Helpful 1
  1. Andreas Mykonios
  2. Friday, 9 June 2023 15:42 PM UTC
Oh.

I just saw you are using any datatype.

I will review that for sure.

Andreas.
  1. Helpful
There are no comments made yet.
Rajesh Selvam Accepted Answer Pending Moderation
  1. Friday, 9 June 2023 16:12 PM UTC
  2. PowerBuilder
  3. # 4

Just an another way to get the column count from dynamic crosstab.

 

Long ll_col_count 
dw_1.modify('datawindow.crosstab.staticmode=yes')
ll_col_count = Long(dw_1.Describe("DataWindow.Column.Count"))
Comment
  1. John Fauss
  2. Friday, 9 June 2023 17:39 PM UTC
Cool! I was not aware of the StaticMode property. Thank you for sharing this!
  1. Helpful
  1. Andreas Mykonios
  2. Friday, 9 June 2023 17:41 PM UTC
Yea.

Very nice. I also wasn't aware. Maybe because I avoid using crosstabs.

Andreas.
  1. Helpful 1
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.