Hi,

I have a request heading my way that I am not sure is achievable. However I am new to WebFocus and am hoping that something may exist to help me out!

We have 2 identically structured files. The first file contains information of a primary stock count, and is keyed by product. The 2nd file contains many records per product and relates to the 2nd, 3rd, 4th etc stock counts.

So what they are looking for is something like this:

PRODUCT___STOCK_____COUNT_NO_____FILE

ABCDE_____00100_____1____________FILEA
ABCDE_____00099_____2____________FILEB
ABCDE_____00102_____3____________FILEB
ABCDE_____00101_____4____________FILEB

So only data from File A to be on row 1 and the following rows to contain data from File B.

The way I would expect the report to work with my current WebFocus experience would be

PRODUCT___FILEA_STOCK_____FILEB_STOCK

ABCDE_____00100___________00099______
ABCDE_____00100___________00102______
ABCDE_____00100___________00101______

But this is not what is wanted!!

Having a think about it, the best solution I can think of is to enquire FileA with the product and output the record structure to a work file. Then to enquire FileB with the product, append the resulting record structure to the work file and finally to run the report over the work file. Can I append to a file in WebFocus?

Well, I hope you guys can understand my jabbering and perhaps I have answered my own question, but any input would be gratefully received!!

Thanks Mark.

Hi Mark,

If your files have exactly the same column names then you could use the MORE syntax -

TABLE FILE FILEA
SUM STOCK
BY PRODUCT
MORE FILEB
END

If they do not have the same column names then a bit of pre-processing would be required.

T

For an example, I have mimiced your files using the GGSALES table -

TABLE FILE GGSALES
SUM UNITS
COMPUTE CNT/I3 = 1;
BY PRODUCT
BY STCD
WHERE STCD EQ 'R1100'
ON TABLE HOLD AS FILEA FORMAT FOCUS INDEX PRODUCT
END

TABLE FILE GGSALES
SUM UNITS
COMPUTE CNT/I3 = IF LAST CNT EQ '' OR PRODUCT NE LAST PRODUCT THEN 2 ELSE LAST CNT + 1;
BY PRODUCT
BY STCD
WHERE STCD NE 'R1100'
ON TABLE HOLD AS FILEB FORMAT FOCUS INDEX PRODUCT
END
-RUN

TABLE FILE FILEA
SUM UNITS
 BY PRODUCT
 BY CNT
MORE
FILE FILEB
END

T

Thanks Tony, I will give that a go and will let you know how I get on.

That Count coding also answers what my next question was going to be!!

Cheers!!