Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
felcar2013
Partner - Creator III
Partner - Creator III

indirect set analysis

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

1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

10 Replies
Not applicable

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

felcar2013
Partner - Creator III
Partner - Creator III
Author

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
Partner - Creator III
Partner - Creator III
Author

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
Former Employee
Former Employee

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
Partner - Creator III
Partner - Creator III
Author

thanks Henric

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

Felipe

Qrishna
Master
Master

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
Partner - Creator III
Partner - Creator III
Author

hi

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

thanks

felipe