VLOOKUP and COUNTIF funtion in BOXI

[Former Member]

Hi,

I am new to Business Objects, so I have very basic knowledge in it. What I am looking is to create some formula like VLOOKUP and COUNTIF function as in excel.

I have two queries:

Query A: List of all old clients

ClientIDs- 123,124,124

ContactDates-01/04/2010,02/04/2010,01/04/2010

Query B: List of clients during some reporting period

ClientIDs: -123, 125,124,126

What I want to do is to identify the old clients in QueryB - and pull the Contact Dates from the Query A to query B. Also the other issue I am facing is because ClientIDs are linked, when I create some funtion it is also populating all the ClientIDs in Query B report which I don't want to happen.

Hope I was able to explain it you

Many Thanks

[Former Member · Accepted solution]

Hi!

1. It's a little bit tricky, but you need to change the object "ContactDates" to the type "Information" for "ClientID". Now extend the query B with an additional Information Objekt.

2. Now link the ClientID from QueryA to ClientID in QueryB

3. And now create a table with the ClientID as content.

4. And a filter on this table where you filter on the Information fields of the opposite query with "=not(isnull(<ContactDates>))" -> this will return all ClientIDs of QueryB which are not in QueryA

[Former Member · Accepted solution]

Thanks for the reply, but unfortunately its not working for me. Here what I did:

*Original Tables to start with*

Table A:

Column1:ClientID

Column2:ContactDate

Table B:

Column1:ClinetID

1. Required Output table ##

Table C:

Column1:[Table B].[ClientID], all clients needs to be listed.

Colum2: Contact Date for clients in Column1 pulled from [Table A].[ContactDate], where there is no matching field then return blank.

Steps I followed:

Step1: Created new variable u201CInformationDateu201D variable type as u201CDetailu201D of [TableA].ContactDate i.e. InformationDate = ContactDate. (ContactDate was Dimension type variable)

Step2: Linked [Table A].[ClientID] to [Table B].[ClientID]

Step3: Created a new table with column1 as [Table B].[ClientID] and column2 as [Table A].[InformationDate]. But this pulled all the ClientIDs from Table A and merged with Table B ClientID.

Step4: Added filter by going into Format>>Filters>>Add>>Variable to Filter=[InformationDate]>>Defite asu201D=Not(IsNull(ContactDate))u201D. But this resulted hiding all the blank cells in column2 (which is InformationDate).

What I wanted is all the ClientID in Table B, if there are no matching ContactDate in Table A then return Blank as some of the clients may be new and do not have a contact date.

Many Thanks

[Former Member · Accepted solution]

Hi!

Okay I did retest this.

if you have following setup:

Table A

client_id Contact_dates

123 01.04.2010

124 02.04.2010

124 01.04.2010

Table B

client_id info_id

122 122

125 125

124 124

126 126

then you can do this via Desktop Intelligence - If you have the possibility to change the universe or sql - you should try to do there your stuff.

So basically you create a dataprovider for Table A : Client_ID .. Dimension, Contact_Dates .. Information for Client_ID.

For Table B: Client_ID .. Dimension, Info_ID .. Information for Client_ID

Now you can join the Client ID from Table A and B.

Create a report table in Deski with following columns:

Client ID ; Contact Dates (where contact dates is filled with this variable: =Max(<Contact_dates>))

Add a variable: = If (IsNull(<info_id>)) Then 1 Else 0

Now add a filter on the table: where the variable is 0

Now you will only see the Clients in Table B and for each client ID you will have only one entry.

I hope this is what you wanted to find.

ciao

[Former Member · Accepted solution]

Thanks very much it did work, however its giving me more records in the TableB then usual I suppose its the duplicates.