indirect set analysis

[felcar2013]

hi

i Need to calculate something like this:

Count(distinct Customer) where field1_ID = field2_ID

Customer and field1_ID are in the same table

field2_ID is located in another table

both tables are indirectly associated (there are like 2-3 tables between both of them)

it should be somthing like

count({<field1_ID = p(field2_ID)>} customer)

but it is not working. Any other idea?

thanks

[#2]

Hi Felipe - field1_ID & field2_ID is the same column such as Customer_ID.

[felcar2013]

Hi Sasi

i think i did not understand fully your question. But field1_ID and field2_ID are columns with different names but they have the same Content. Field1_ID and customer columns are located in the same table, so i have to Count customers under the condition that field1_ID = field2_ID

[sunny_talwar]

May be this:

Count(If(field1_ID = field2_ID, customer))

[felcar2013]

hi

this may be too slow for 600 Million records, i am currently loading, but will do that, it takes a time

thanks

[sunny_talwar]

May be create a flag in your script:

If(field1_ID = field2_ID, 1, 0) as Flag

and then may be this:

Count({<Flag = {1}>} customer)

[hic · Accepted solution]

Your expression will also find Customers where field1_ID = field2_ID, as long as field1_ID matches any of the possible values of field2_ID.

No, you want a comparison that is made row by row, and this cannot directly be made with Set Analysis. Try

Count(distinct If(field1_ID = field2_ID,Customer))

or

Sum( Aggr( If( Max(field1_ID) = Max(field2_ID),1,0) ,Customer))

HIC

[felcar2013]

thanks Henric

I will try this once my over 600 m. row tables are loaded

Felipe

[Qrishna]

count({<field1_ID = p(field2_ID)>} customer) is on possible solution.

if that doesnot work,

Try:

count({<KeyAutoNumber  = {"=(field1_ID = field2_ID)"}>} customer)

Where KeyAutoNumber is an integer key associating both fields.

You can also Use IF() statement but will be slower.

You can do this script by writing:

tab1:

Load ..,

field2_ID,

..,

from pqr.qvd;

tab2:

Load

Customer ,

field1_ID

....

From xyz.qvd

where exists(field2_ID,field1_ID );

Do Regular count on Front end

[felcar2013]

hi

the first one was correct and fast for more than 600 Million records

thanks

felipe