Focal Point
[SOLVED] Use an Alias in HOLD file

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1937021196

February 19, 2019, 11:03 AM
Shingles
[SOLVED] Use an Alias in HOLD file
So... my problem is this. I have two hold files HOLD1 and HOLD2. HOLD1 has a column named COLUMN and so does HOLD2. If I join HOLD1 and HOLD2, and then query COLUMN from HOLD2 from the resulting joined table (HOLD1), I only end up getting values from COLUMN in HOLD1. I understand that webfocus looks for the first column name and that is why it is taking COLUMN from HOLD1, but I can't figure out a way around this.

COLUMN from HOLD1 and HOLD2 are both needed, but I can't seem to get values from COLUMN in HOLD2 after the join is made. I tried to create aliases by way of using 'AS' in HOLD1 and HOLD2 (BY COLUMN AS 'HOLD1_COLUMN' for HOLD1 and BY COLUMN AS 'HOLD2_COLUM' for HOLD2), but the aliases don't stick. I only see COLUMN when I spit out the results of the join.

Can someone point me in the right direction here?

Thank you!

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 19, 2019, 11:12 AM
BabakNYC
When I HOLD data for this type of situation, I always add meaningful titles to each column that'll make them unique and:
SET ASNAME=ON
SET HOLDLIST=PRINTONLY


WebFOCUS 8206, Unix, Windows
February 19, 2019, 11:54 AM
Tony A
Also consider using TAGs -

JOIN field IN TABLE1 TAG T1 TO [unique/multiple] field IN TABLE 2 TAG T2 AS J1

TABLE FILE TABLE1
BY T1.field
BY T2.field
etc...


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
February 19, 2019, 12:12 PM
Shingles
Thanks for the quick replies... you guys rock.

BabakNYC... will SET HOLDLIST=PRINTONLY make it so that I can only use PRINT as a verb? Could I still use SUM and BY?

In the meantime, I'll try the TAG approach.

Thank you!


WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 19, 2019, 01:31 PM
MartinY
quote:
Originally posted by Shingles:
BabakNYC... will SET HOLDLIST=PRINTONLY make it so that I can only use PRINT as a verb? Could I still use SUM and BY?

Yes.
PRINTONLY means keep displayed fields only no matter if it's a BY, ACROSS, SUM, PRINT or COMPUTE (for COMPUTE only the resulting field is kept, not the internal references).
As per example, a field with a NOPRINT will not be included using the HOLDLIST=PRINTONLY


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
February 19, 2019, 01:38 PM
Waz
quote:
will SET HOLDLIST=PRINTONLY make it so that I can only use PRINT as a verb? Could I still use SUM and BY?


Shingles, it may be worth your while to check out the documentation to understand what it does.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

February 19, 2019, 07:27 PM
Addy
quote:
Originally posted by Tony A:
Also consider using TAGs -

JOIN field IN TABLE1 TAG T1 TO [unique/multiple] field IN TABLE 2 TAG T2 AS J1

TABLE FILE TABLE1
BY T1.field
BY T2.field
etc...


T


In your case TAG will solve your problem for sure. If you dont want to use tag then go for TableName.FieldName

This will come in handy if you are using cluster master files and are not specifying your joins in the code.


WF 8.2.04
Windows/Unix
All Formats
In Focus since 2006
February 21, 2019, 10:34 AM
Hallway
Check out the docs on Controlling Attributes in HOLD Master Files


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
February 21, 2019, 11:56 AM
Shingles
Just a quick update... the TAG route didn't work for me. Webfocus wasn't able to find T2.COLUMN.

I went with the PRINTONLY route and it seems good so far.

Thanks again folks!


WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 21, 2019, 03:15 PM
Shingles
Follow up question... I'm not sure if this warrants a new thread, but I wanna do what I can to not flood the forums...

So, I'm using PRINTONLY and I am now able to see the aliases I used after the join... to recap my code looks something like this:

   
SET ASNAME=ON
SET HOLDLIST=PRINTONLY

TABLE FILE WHATEVER
BY COLUMN AS 'HOLD1_COLUMN'
BY UNIQUE_KEY
ON TABLE HOLD AS HOLD1
END

TABLE FILE SOMETHING
BY COLUMN AS 'HOLD2_COLUMN'
BY UNIQUE_KEY
ON TABLE HOLD AS HOLD2
END

JOIN LEFT_OUTER UNIQUE_KEY IN HOLD1 TO ALL UNIQUE_KEY IN HOLD2 AS J11

TABLE FILE HOLD1
PRINT * ON TABLE SET PAGE-NUM NOPAGE ON TABLE SET LINES 999999
END


The resulting HOLD1 table shows HOLD2_COLUMN as . when a join isn't made (but shows the correct information when a join is made). Which I get... its a null record. But I would like for HOLD2_COLUMN to show as 0 instead.

I've tried to create some DEFINE fields to accomplish this...

NEW_HOLD2_COLUMN/D9.6 MISSING OFF = HOLD2_COLUMN;

NEW_HOLD2_COLUMN/D9.6 MISSING ON = HOLD2_COLUMN;

NEW_HOLD2_COLUMN/D9.6 = IF HOLD2_COLUMN EQ MISSING THEN 0 ELSE HOLD2_COLUMN;

NEW_HOLD2_COLUMN/D9.6 = IF HOLD2_COLUMN NE MISSING THEN 0 ELSE HOLD2_COLUMN;


Now admittedly... I got a little desperate, and I just started typing stuff that didn't even make sense to me, but none of those worked. Any tips here?

Thanks again folks!


WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 22, 2019, 07:43 AM
MartinY
Your issue is probably not because of missing HOLD2_COLUMN value but a missing child
Try this instead
JOIN
 LEFT_OUTER UNIQUE_KEY IN HOLD1 TAG T1
     TO ALL UNIQUE_KEY IN HOLD2 TAG T2 AS J11
END

DEFINE FILE HOLD1
NEW_HOLD2_COLUMN/D9.6 = IF T2.UNIQUE_KEY EQ MISSING THEN 0 ELSE HOLD2_COLUMN;
END

TABLE FILE HOLD1
PRINT *
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE SET LINES 999999
END

Or take a look at SET ALL options, MULTIPATH, SHORTPATH and JOIN definition
Handling a Missing Segment Instance
Selections Based on Individual Values
SHORTPATH
Joining Data Sources
And you can find much more documentation regarding JOIN and all the options


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
February 22, 2019, 08:38 AM
Joanna Swiggett
I just wanted to point out that SET ASNAME=ON should be SET ASNAMES=ON


WebFOCUS 8.2.01M
February 22, 2019, 10:41 AM
Shingles
Thanks Joanna...

Martin... That's not getting the expected results. I could use a MORE (which I just call a UNION) to get me zeroes. I've don't that plenty of times.

Thank you


WebFOCUS 8201, SP 0.1, Windows 7, HTML
February 22, 2019, 10:54 AM
MartinY
Maybe having this added at the beginning of you code :
SET NODATA = 0


Or can you try this ?
SET NODATA = 0

MATCH FILE HOLD1
PRINT HOLD1_COLUMN
BY UNIQUE_KEY
RUN
FILE HOLD2
PRINT HOLD2_COLUMN
BY UNIQUE_KEY
AFTER MATCH HOLD AS MATCHDATA OLD
END
-RUN

DEFINE FILE MATCHDATA
NEW_HOLD2_COLUMN/D9.6 = IF HOLD2_COLUMN EQ MISSING OR 0 THEN 0 ELSE HOLD2_COLUMN;
END
TABLE FILE MATCHDATA
PRINT HOLD1_COLUMN
      HOLD2_COLUMN
      NEW_HOLD2_COLUMN
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE SET LINES 999999
END
-RUN



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
February 22, 2019, 11:14 AM
Shingles
Thanks for the persistence Martin...

I did try the 'EQ MISSING OR 0' route before with no success. I forgot about 'SET NOTDATA = 0' though.

I did solve the problem with MORE, and I have some more pressing things to do at the moment. But when I get a few minutes, I'll try what you said.

Thank you again!


WebFOCUS 8201, SP 0.1, Windows 7, HTML