table format

[creedprg]

Hello Guys

i am trying to create a output my table in a different format. It shows like this

this is the original format of my table

original table
Modelno....defect_type......qty
test1.......scratch..........10
test2.......dent.............12
test3.......deform...........20
test4......scratch..........10

output
defect_type......test1........test2......test3.....test4
scratch.............10.......................................10
dent................................12
deform.........................................20

the record becomes my field...


Hope you can help me...
thanks

 

[vgulielmus]

You can use fastXtab. The updated version is here Link

More information you can find here Link

The original class can be found here Link

All you have to do is :

USE original

oXtab = NewObject("FastXtab", "FastXtab.prg")
oXtab.lCursorOnly = .T. && if you need the output as dbf set it as .F.
oXtab.cRowField='defect_type'
oXtab.cColField='Modelno'
oXtab.cDataField='qty'
oXtab.cOutFile="output"
oXtab.RunXtab()

SELECT output
BROWSE

Demo

CREATE CURSOR original (Modelno C(20),defect_type C(20),qty I)
INSERT INTO original VALUES ('test1','scratch',10)
INSERT INTO original VALUES ('test2','dent',12)
INSERT INTO original VALUES ('test3','deform',20)
INSERT INTO original VALUES ('test4','scratch',10)

USE original
oXtab = NewObject("FastXtab", "FastXtab.prg")
oXtab.lCursorOnly = .T.
oXtab.cRowField='defect_type'
oXtab.cColField='Modelno'
oXtab.cDataField='qty'
oXtab.cOutFile="output"
oXtab.RunXtab()

SELECT output
BROWSE

If you want Null values instead of 0, then :
oXtab.lDisplayNulls=.T.

By default the numeric values are summed.

oXtab.nFunctionType=1 && Sum (Default for numeric)
oXtab.nFunctionType=2 && Count
oXtab.nFunctionType=3 && Avg
oXtab.nFunctionType=4 && Min
oXtab.nFunctionType=5 && Max (Default for character)
oXtab.nFunctionType=6 && Custom

If you use nFunctionType=6 then you must define an expression like:
oXtab.cFunctionExp="SUM(qty)/2" && For nFunctionType=6

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Mike Lewis]

Creedprg,

The problem with what you want to achieve is that you will have to decide in advance how many tests you will allow for each defect. Your example shows ten tests for a scratch, 12 tests for a dents, and so on. But your record needs a fixed number of fields. How do you propose to handle that?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I don't see that problem, Mike. The number of tests is not the number of columns, but the data in the below schema.

The cross tab wizard Vilhelm-Ion Praisach links to should work, also VFPs own _GENXTAB.

Bye, Olaf.

 

[Mike Lewis]

That's not what I understood, Olaf. Are you saying he's always got exactly four tests, and therefore five columns?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

No, but then I understood you wrong, I thought you werre talking about the qty values 10,12, and 20.
The cross table wizard or Vilhelm-Ion will generate as many test columns as needed from the data, that's not a problem, unless there would be more than 253 different Modelno values.

Bye, Olaf.

 

[Mike Lewis]

OK, I can see why that would be confusing. I was really referring to the open-ended number of columns. I agree that the cross-tab wizard can handle it, but I was wondering if it's what Creedprg really wants.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[creedprg]

Hello masters

thank you for all the response on this thread. i finally got the solution to this. the idea is to transfer the data from my dbf table to excel file yet in a different format, the records become the field(header in excel). Mike was right it i have to limit the record in order to avoid further problem. the problem solve by using loop command and directly write it in excel. it did the trick.


thanks Guys.

 

[Olaf Doschke]

Using Excel to put in data transposed is surely a solution, too.

You still may try to simply start the vfp cross tab wizard. You find it in Tools menu Wizards->All Wizards->Cross-Tab Wizard. It'll ask for your original data, what you want as columns, as rows and as cell data and whether that will be counted, summed or whatever. The output will be a qry file, which is a type of a prg, so you can see how it finally calls _GENXTAB. The final cursor you have could then be exported to excel via COPY TO TYPE XLS.

The description of the cross tab wizard is "Displays your data in a spreadsheet format", so that also suggests using Excel is a way to get there.

Bye, Olaf.