Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Condicional Joint using only infoassist

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Condicional Joint using only infoassist
 Login/Join
 
Member
posted
Hi all,

Newbie here.

Can a user that can only use infoassist (with no access to code), do the following?

I have a table that looks like this
ID, Start Date, End Date, ....
1, 1/1/2019, 1/5/2019, ...
...
...
9, 1/2/2019 1/6/2019, ...
....

I want to make a report that does this:
Date, number of records
1/1/2019, 1
2/1/2019, 2
3/1/2019, 2
4/1/2019, 2
5/1/2019, 2
6/1/2019, 1

Can you do this with infoassist only, without coding it directly?

Thanks.

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


Infoassist 8.1
windows 10
excel, pdf
 
Posts: 6 | Location: brazil | Registered: December 03, 2019Report This Post
Virtuoso
posted Hide Post
Jack0890: It's unclear what you'd like to accomplish. There's a Join in InfoAssist. Could you show us the data in the two tables and what you want the answer set to look like? I can't tell what your SQL is trying to output.


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Virtuoso
posted Hide Post
Babak,

I think that the important part of Jack reside into the post subject : "Conditional Joint using only InfoAssist"

My client is not an IA user so I cannot help on how to perform that with IA


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
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
Thanks for replying.

I have a table that looks like this
ID, Start Date, End Date, ....
1, 1/1/2019, 1/5/2019, ...
...
...
9, 1/2/2019 1/6/2019, ...
....

I want to make a report that does this:
Date, number of records
1/1/2019, 1
2/1/2019, 2
3/1/2019, 2
4/1/2019, 2
5/1/2019, 2
6/1/2019, 1

So, we just want to count the number of records that falls within a date range.
But, again, we can only use InfoAssist (IA, I guess), and we cant change the code directly. We only have access to the Join dialog box.

This message has been edited. Last edited by: Jack0890,


Infoassist 8.1
windows 10
excel, pdf
 
Posts: 6 | Location: brazil | Registered: December 03, 2019Report This Post
Virtuoso
posted Hide Post
In InfoAssist JOIN dialogue, you can click Filter to add a WHERE test to the JOIN. For example this is the code I created in InfoAssist GUI by adding BRAND=Canon to the JOIN.
  
JOIN INNER FILE ibisamp/facts/wf_retail_sales AT WF_RETAIL_SALES.WF_RETAIL_SALES.ID_PRODUCT
TO UNIQUE FILE ibisamp/dimensions/wf_retail_product AT WF_RETAIL_PRODUCT.WF_RETAIL_PRODUCT.ID_PRODUCT TAG J001 AS J001
WHERE J001.WF_RETAIL_PRODUCT.BRAND EQ 'Canon';
END


So, in this example I've joined wf_retail_sales to wf_retail_product using ID_PRODUCT but I also have a condition WHERE BRAND EQ 'Canon';

All of this is generated within the GUI.

This message has been edited. Last edited by: BabakNYC,


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
We thought about doing it using DEFINE, but couldnt come up with a good formula.

I mean, how would you, for exemple, count a record like this:
ID, Start Date, End Date, ....
1, 1/1/2019, 1/5/2019, ...
9, 1/2/2019 1/6/2019, ...

IF Start Date GE '1/1/2019' AND End Date LE '1/2/2019'THEN 1
ELSE IF Start Date GE '1/1/2019' AND End Date LE '1/3/2019'THEN 2
ELSE IF Start Date GE '1/1/2019' AND End Date LE '1/4/2019'THEN 3
...

This wouldnt be a long run solution to the problem...

If somehow you could join the table with itself and create a table like this:
ID, Start Date, End Date, Date
1, 1/1/2019, 1/5/2019, 1/1/2019
1, 1/1/2019, 1/5/2019, 1/2/2019
1, 1/1/2019, 1/5/2019, 1/3/2019
1, 1/1/2019, 1/5/2019, 1/4/2019
1, 1/1/2019, 1/5/2019, 1/5/2019
9, 1/2/2019, 1/6/2019, 1/2/2019
9, 1/2/2019, 1/6/2019, 1/3/2019
9, 1/2/2019, 1/6/2019, 1/4/2019
9, 1/2/2019, 1/6/2019, 1/5/2019
9, 1/2/2019, 1/6/2019, 1/6/2019

It would be a lot easier to count.

Anway, I am open to any suggestions, given that I am stuck...


Infoassist 8.1
windows 10
excel, pdf
 
Posts: 6 | Location: brazil | Registered: December 03, 2019Report This Post
Virtuoso
posted Hide Post
Create a define in InfoAssist that concatenates the start date and end date. Then count ID by the concatenated field. You'll have to use the EDIT2 function to convert the dates into a string first.

I used Orders in Northwind to do this.

  
-*COMPONENT=Define_orders
DEFINE FILE ibisamp/orders
CONC_DT/A100=EDIT2( ORDERS.ORDERS.ORDERDATE ,'HYYMD')||EDIT2( ORDERS.ORDERS.REQUIREDDATE ,'HYYMD');
END
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_ARVERSION=1;
SET ARVERSION=&WF_ARVERSION

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE ibisamp/orders
COUNT ORDERS.ORDERS.ORDERID
BY CONC_DT
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, ORIENTATION=LANDSCAPE, $
ENDSTYLE
END

-RUN


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Member
posted Hide Post
I got what you did. It is almost what I need.
This would count similar records like:

if my sample table is:
ID, Start Date, End Date, ....
1, 1/1/2019, 1/5/2019, ...
8, 1/1/2019, 1/5/2019, ...
9, 1/2/2019 1/6/2019, ...

that would give me a report like:
CONC_DT, #records
1/1/20191/5/2019, 2 (ID 1 and 8)
1/2/20191/6/2019, 1 (ID 9)

But I need the count in between those dates (start/end), so
the 1 of january there would be 2 record
the 2 of january there would be 3 records (ID 1, 8 and 9) and so on
and on the 6 of january there would be 1 record.


Infoassist 8.1
windows 10
excel, pdf
 
Posts: 6 | Location: brazil | Registered: December 03, 2019Report This Post
Member
posted Hide Post
I think i may have found what i need. MathematicalRobs answer is what i am trying to do.
now that i know it is possible, have to check if i can do it with IA only.

http://forums.informationbuild...757091226#9757091226


Infoassist 8.1
windows 10
excel, pdf
 
Posts: 6 | Location: brazil | Registered: December 03, 2019Report This Post
Member
posted Hide Post
It worked! Smiler Thanks for all the help.


Infoassist 8.1
windows 10
excel, pdf
 
Posts: 6 | Location: brazil | Registered: December 03, 2019Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Condicional Joint using only infoassist

Copyright © 1996-2020 Information Builders