COGNOiSe.com - The IBM Cognos Community

ETL, App Development and other tools => COGNOS DecisionStream/Data Manager => Topic started by: gatorfe on 14 Mar 2014 11:05:59 AM

Title: ETL Filter
Post by: gatorfe on 14 Mar 2014 11:05:59 AM
We have a sales order table in our erp system that we are extracting from in DM.  We dont want to extract line 1,3,5 for order number 23565.  What is the syntax to use to accomplish this?  Thank you in advance for any suggestions.
Title: Re: ETL Filter
Post by: MFGF on 14 Mar 2014 11:45:42 AM
You can either add a filter expression on the Filter tab of your datastream with an expression such as

(OrderNumber = 23565 and OrderLineNumber not in (1,3,5)) or (OrderNumber <> 23565)

or you could add the same logic as a predictate (WHERE clause) to the SQL of the data source.

Cheers!

MF.
Title: Re: ETL Filter
Post by: gatorfe on 14 Mar 2014 12:19:43 PM
Thank you for the input, we prefer to do it at the Query tab of the Data Source Properties.  This is the syntax we have:
select
soi.slord as ORDER_NO,
soi.slline as ORDER_LINE_NO
from
erp.svkincd
where
(slord <> 249486 and slline not in (1,3,5))

When we run it it does not show us any line 1,3,5 at all.  We just dont want to see it for order 249486.  Is there a way to get around that?
Title: Re: ETL Filter
Post by: MFGF on 14 Mar 2014 03:51:05 PM
Quote from: gatorfe on 14 Mar 2014 12:19:43 PM
Thank you for the input, we prefer to do it at the Query tab of the Data Source Properties.  This is the syntax we have:
select
soi.slord as ORDER_NO,
soi.slline as ORDER_LINE_NO
from
erp.svkincd
where
(slord <> 249486 and slline not in (1,3,5))

When we run it it does not show us any line 1,3,5 at all.  We just dont want to see it for order 249486.  Is there a way to get around that?

Hi,

I already gave you the logic?

Quote from: MFGF on 14 Mar 2014 11:45:42 AM

(OrderNumber = 23565 and OrderLineNumber not in (1,3,5)) or (OrderNumber <> 23565)

The only difference is your item names...

where
(slord = 249486 and slline not in (1,3,5)) or (slord<> 249486 )

Cheers!

MF.
Title: Re: ETL Filter
Post by: gatorfe on 14 Mar 2014 05:21:51 PM
Sorry about that, I tried your complete logic((slord = 249486 and slline not in (1,3,5)) or (slord<> 249486 )) but its not working.  Before the logic there are 1863 records.  When I add your logic it goes to 20000 rows.  Any ideas why that would be?
Title: Re: ETL Filter
Post by: MFGF on 01 Apr 2014 08:00:07 AM
What other filtering is being done in the WHERE clause? You would need to take account of this also...

MF.