• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Union all my cursors but need to get back to a certain format/layout

I need to get back to the format/layout that the IIF() did for me
it did it like:
user      cut    po     ship    stage   moves .....
user1    0        5       15       30         2
user2    9        0        30      15          19
......

my current UNION, since the IIF() got too complicated with all the conditions per each source code. It is easier to make the 9 different cursors and now Union them all on user and source

but it does no come out in the same format/layout

I will attach the two different ones in excel.
select iruser, ;
		SUM((IIF(irsrc='F' AND BLGRP!='35VNL' AND irqty<0,1,0)) +  (IIF(irsrc='P' AND BLGRP!='35VNL',1,0)) + ((IIF(irsrc='T' AND BLGRP!='35VNL' ;
			 AND irqty>0,1,0)) + (IIF(irsrc='G' AND BLGRP!='35VNL' AND irqty<0,1,0)) + ;	
			(IIF(irsrc='D' AND irqty<0 AND blgrp!='35VNL',1,0)) + (IIF(irsrc='J' AND BLGRP!='35VNL' AND ;
			irqty<0,1,0)) + (IIF(irsrc='H' AND irqty<0 AND blgrp!='35VNL',1,0)))) as total_lines, ;
		sum(IIF(irsrc='F' AND BLGRP!='35VNL' AND irqty<0,1,0)) as cuts, ;
		SUM(IIF(irsrc='P' AND BLGRP!='35VNL' ,1,0)) as po_lines_recvd, ;
		sum(IIF(irsrc='T' AND BLGRP!='35VNL' AND irqty>0,1,0)) as xe_lines_recvd, ;
		SUM(IIF(irsrc='G' AND BLGRP!='35VNL' AND irqty<0,1,0)) as staged_lines, ;
		SUM(IIF(irsrc='D' AND BLGRP!='35VNL' AND irqty<0,1,0)) as shipped_lines, ;
		SUM(IIF(irsrc='J' AND BLGRP!='35VNL' AND irqty<0,1,0)) as consolidated_lines, ;
		SUM(IIF(irsrc='H' AND BLGRP!='35VNL' AND irqty<0,1,0)) as moved_lines ;
		from cItemrech, cBinloc, cUserfile, cUserxtra, cprempm ;
		where (irloc=41 OR irloc=57)  AND irbin=blbin AND irloc=blloc AND (usloc=41 OR usloc=57) AND usid=iruser AND usxid=usid AND ;
				ememp_=usxemp_  and (emloc!=38 AND emloc!=96 AND emloc!=52) AND blgrp!='SAMPL';
		group by usxemp_ ;
		order by iruser into cursor cResultsIR readwrite

Open in new window

pm-user-productivity.xls
union-format.xls
0
jaymz69
Asked:
jaymz69
  • 5
  • 4
1 Solution
 
jaymz69Author Commented:
Code for the union

* UNION 
*
select cCuts.iruser, cCuts.irsrc from cCuts  ;
union ;
select cPO.iruser, cPO.irsrc from cPO   ;
union ;
select cXERecv.iruser, cXERecv.irsrc from cXERecv  ;
union ;
select cStage.iruser, cStage.irsrc from cStage  ;
union ;
select cTrucks.iruser, cTrucks.irsrc from cTrucks  ;
union ;
select cXEShip.iruser, cXEShip.irsrc from cXEShip  ;
union ;
select cConsolidate.iruser, cConsolidate.irsrc from cConsolidate  ;
union ;
select cMoves.iruser, cMoves.irsrc from cMoves;
union ;
select cWillCall.iruser, cWillCall.irsrc from cWillCall ;
order by irsrc into cursor cUnion readwrite
 

Open in new window

0
 
jaymz69Author Commented:
So I need to use Union to get the filters right for each code
but I need the layout in my results as how the IIF() comes out.

Thanks and Happy Holidays to All!
0
 
pcelbaCommented:
The cUnion cursor is a good start but it is not possible to convert it into requested format because it does not contain any numbers...

If you'll create the union cursor with irqty column (or with any numeric column which should be on output) and define required aggregate operations on this numeric column (SUM, COUNT, etc.) then you may use the Cross-tab Wizard to create table in a format which you need. Aggregate operations are not necessary if you just need to transpose rows and columns of your cursor.

You could also use some dynamic code to populate your output table but it would probably be more complex than your SQL select containing all IIFs.

BTW, data in above two Excel sheets do not correspond - some users visible in Union cursor are not included in the user productivity cursor.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pcelbaCommented:
Let suppose your union cursor would contain the third column irqty which will be summarized on output. irsrc column will be used for transposed column names. Cross-tab Wizard is very easy to use (it has user friendly UI) and it will generate code similar to the following one:
SELECT cUnion.iruser, cUnion.irsrc, SUM(cUnion.irqty);
    FROM cUnion;
    GROUP BY cUnion.iruser, cUnion.irsrc;
    ORDER BY cUnion.iruser, cUnion.irsrc;
    INTO CURSOR SYS(2015)
DO (_GENXTAB) WITH 'Query',.t.,.t.,.t.,,,,.t.,0,.t.
BROWSE NOMODIFY

*-- _GENXTAB contains a name  of the cross-tab generator which contains additional useful info (obviously it is C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\VFPXTAB.PRG)

Open in new window

0
 
jaymz69Author Commented:
I am just trying to count up how many transaction a user had on a source code. Due to how complex the database is with its way of how it stores all the data I have to open 5 tables and sort out a bunch of things. It turned my simple IIF() into a jumble. so I went with the Union approach after querying all the different source codes
0
 
pcelbaCommented:
Union is not a problem but you really need some numbers which should be cross-tabbed on output.
0
 
jaymz69Author Commented:
well I could count() the sourceCode in my unions...
that should give it a count/number for that code.
0
 
pcelbaCommented:
Yes, it should work.
0
 
pcelbaCommented:
Cross-tabbing is discussed in related question here: http://www.experts-exchange.com/Microsoft/Applications/FoxPro/Q_25005689.html
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now