Alpha Video Training
Results 1 to 6 of 6

Thread: Crosstab extract comma deliminated field

  1. #1
    Member
    Real Name
    Denis Ahmet
    Join Date
    Jul 2005
    Location
    United Kingdom
    Posts
    988

    Default Crosstab extract comma deliminated field

    Hi,

    I have a table(tbl_issues) with the following structure;

    ID------TITLE-----PROJECT-------STATUS
    01------Blue------P1,P4,P6------Closed
    02------Pink------P3,P1,P5,P2---Open
    03------Red-------P2------------Standby
    04------Green-----P8,P7---------Closed

    I need to create a crosstab that will break up the PROJECT comma deliminated field and provide the following table.

    PROJECT---CLOSED----OPEN----STANDBY
    P1----------1--------1---------0---
    P2----------0--------1---------1---
    P3----------0--------1---------0---
    P4----------1--------0---------0---
    P5----------0--------1---------0---
    P6----------1--------0---------0---
    P7----------1--------0---------0---
    P8----------1--------0---------0---

    Can anyone show me how. I have attempted for hours, but am having a real problem breaking up the comma deliminated field.

    Any help will be greatly appreciated.

    Denis

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Crosstab extract comma deliminated field

    You need to process the table first to create separate records for each of the entries in the comma separated field, then crosstab that table for the final result.
    There can be only one.

  3. #3
    Member
    Real Name
    Denis Ahmet
    Join Date
    Jul 2005
    Location
    United Kingdom
    Posts
    988

    Default Re: Crosstab extract comma deliminated field

    Stan,

    Thank you for your response.

    I can do the crosstab bit. Can you offer any assitance on processing the table in preperation for the corosstab?

    Denis

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Crosstab extract comma deliminated field

    Save and run this. Then examine the result table "for_xtab".

    Code:
    t = table.open("tbl_issues")
    if file.exists(a5.Get_Path()+chr(92)+"for_xtab.dbf")
    	file.remove(a5.Get_Path()+chr(92)+"for_xtab.dbf")
    end if
    t.duplicate(a5.Get_Path()+chr(92)+"for_xtab.dbf")
    tgt = table.open(a5.Get_Path()+chr(92)+"for_xtab.dbf")
    file_add_to_db(a5.Get_Path()+chr(92)+"for_xtab.dbf")
    t.order("id")
    t.fetch_first()
    while .not. t.fetch_eof()
    	entries = w_count(t.project,",")
    	for i = 1 to entries
    		tgt.enter_begin()
    		tgt.id = t.id
    		tgt.title = t.title
    		tgt.project = word(t.project,i,",")
    		tgt.status = t.status
    		tgt.enter_end(.t.)
    	next i
    	t.fetch_next()
    end while
    tgt.close()
    t.close()
    There can be only one.

  5. #5
    Member
    Real Name
    Denis Ahmet
    Join Date
    Jul 2005
    Location
    United Kingdom
    Posts
    988

    Default Re: Crosstab extract comma deliminated field

    Thanks Stan. That looks great. I will integrate this into my code.

    Denis

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Crosstab extract comma deliminated field

    If you are going to report on the restructured table you will probably want to create one more table with the

    PROJECT---CLOSED----OPEN----STANDBY

    structure.

    You can now empty that table (each time) and append the records obtained via my script. Since you now base your reporting on the final table the reports are not lost each time you run the script removing the prior result table.
    There can be only one.

Similar Threads

  1. Lookup values from a comma seperated field
    By den1s in forum Application Server Version 9 - Web/Browser Applications
    Replies: 35
    Last Post: 10-17-2010, 09:20 PM
  2. Populate user_id field in crosstab
    By den1s in forum Application Server Version 9 - Web/Browser Applications
    Replies: 5
    Last Post: 02-16-2010, 11:56 AM
  3. Extract word from one field to another
    By jpayne in forum Alpha Five Version 8
    Replies: 10
    Last Post: 09-21-2007, 10:23 PM
  4. Control Crosstab field names
    By Stephen Williams in forum Archived Wishlist
    Replies: 2
    Last Post: 10-27-2005, 12:06 PM
  5. Comma or Period after field in RTF
    By gyoung in forum Alpha Five Version 6
    Replies: 13
    Last Post: 10-13-2005, 12:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •