Format EXPORT XLS from a JOIN CURSOR 2

[dexterdg]

Whenever i do join two tables and export them into an xls/xl5, its working fine but i get these repeated records to fill up the spaces. is there a way to avoid that and make it look neat?

thanks!
Dexter

 

[Dan Freeman]

I'm guessing you need either a left or right inner join rather than the default, but with no details about what you're doing that is just a guess.

 

[dexterdg]

Everything is working fine and i want my xls look neat. Its regarding this thread thread184-1701489. I can do now the report, what i can export it to xls what i want is my xls not to print/fill cells repeatedly when you join two tables.

Already tried LEFT, RIGHT, INNER, OUTER, FULL and the default JOIN. same results. the output i want is something like FULL JOIN when joining two tables and there's no match found on the other table it prints blank. In the JOIN, if table_one has a one(1) record and if table_two as three(3) match(Primary key), it will print 3:3(three repeated item on table_one and three different items on table_two) w/c is what i dont want. Can it be 1:3 on outputing xls?

 

[Olaf Doschke]

In a report you can say to not print repeated values. In a join you can't set fields NULL in repeated records (having N joins in a 1:n relation) but only in missing records (having no join).
That's the way SQL is.

If you can make the result cursor nullable in the 1 side, you could NULL the records with identical primary key.

I don't know if there is an Excel formatting with the same effect of not printing repeated values as report controls can. But you could change to FoxyPreviewer to let it export Excel from a report and that should include that report feature to not print repeated values.

Bye, Olaf.

 

[Mike Lewis]

One possible approach would be to do a separate pass of the cursor, explicitly removing the repeated values.

First, make sure the cursor is in the order represented by the grouping value (the value that controls the groups in which the repeating values occur). If necessary, create an index to do that.

Then, scan the table. For each record, if the grouping value is different from that in the previous record, blank out the values that you don't want to repeat (set them to NULL, or to an empty string, or zero, or whatever).

Finally, use the cursor to generate the XLS, as before.

I hope this makes sense. If it's not clear, show us an example of the data that you are working with, and I will try to show you some code.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Yes,

Mikes idea is straight forward, the only downside is, a query won't create all fields nullable, only joined fields.
I did a query of the type Select fieldlist From Parent Left Join Child on Child.parentid = Parent.id readwrite
and only the fields from the child table are nullable.

I can replace query result fields coming from the child table with .NULL., but not parent fields. You could CAST all parent fields to make them nullable, but it's not very practical.

You could add a bit of trickery here, and add another virtual parent table layer to the select, so the real parent table also is joined:

Create Cursor virtualparent (dummy I)
Append Blank
Select fieldlist FROM virtualparent LEFT JOIN Parent ON .T. Left Join Child on Child.parentid = Parent.id readwrite

Having one record in virtualparent and not putting it's dummy field into the resultsets field list, you get the same result as before, but as the parent table is left joind, all fields of the query result are nullable, too.

Now you can use Mikes idea to set fields NULL. That works better especially in all non text fields, because an empty date or 0 won't create an empty cell in Excel, but NULLs will show empty, AFAIR.

Bye, Olaf.

 

[Mike Lewis]

It's just occurred to me .... do we want to end up with:

(i) All the records, but with just the repeating fields blanked out; or

(ii) Only one record for each of the controlling fields.

If (i), then my previous suggestion still applies. In that case, you don't want an inner join.

If (ii), then another way of doing it would be with a GROUP BY clause.

Dexter, if you could perhaps show us a small sample of the data and the desired result*, it would help to understant the problem.

* wrap any data in [ignore]

[/ignore] tags.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

[url=http://www.ml-consult.co.uk]Visual FoxPro articles, tips, training, consultancy[/url]

 

[dexterdg]

this is what ive done:

SELECT * FROM tbl_tblRecord JOIN tbl_tblItem ON tbl_tblRecord.pk=tbl_tblItem.pk INTO CURSOR combnation

this combines my two tables into one and you see as i SELECT combnation, EXPORT [path]\[filename].XLS TYPE XLS. Exported excel looks messy.

and sir Mike said SCAN FOR THE REPEATING VALUES(editing the combnation cursor and making another one) SELECT it and deploy it for EXPORT-ing. I dont get that clearly, how can I make values(on scanning the combnation cursor) .NULL.?

thread184-1701489 is my post about how to join two tables and have an output as illustrated there. what i now want is the same on excel. A 1:n relation output.

 

[dexterdg]

in addition, ReportWriters have this DataGrouping feature that scans to group records. Its something like that, i want to accomplish in coding.

thanks
Dexter

 

[Olaf Doschke]

If you select into cursor combination READWRITE you can write in the result cursor. You can use REPLACE FIELD WITH .NULL. to set a field null. You do a SCAN..ENDSCAN and compare eg PK field with the previous. If that's equal you know the parent table fields all are equal and you want to .NULL. them.

Bye, Olaf.

 

[dexterdg]

Done!

Thanks
Dexter