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,

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.

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

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,

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,

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...

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

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.

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

It worked! Thanks for all the help.