topic Re: indirect set analysis in QlikView

indirect set analysis

felcar2013 — Thu, 15 Oct 2015 13:13:25 GMT

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

Re: indirect set analysis

Thu, 15 Oct 2015 13:16:33 GMT

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

Re: indirect set analysis

felcar2013 — Thu, 15 Oct 2015 13:20:27 GMT

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

Re: indirect set analysis

sunny_talwar — Thu, 15 Oct 2015 13:20:47 GMT

May be this:

Count(If(field1_ID = field2_ID, customer))

Re: indirect set analysis

felcar2013 — Thu, 15 Oct 2015 13:22:12 GMT

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

thanks

Re: indirect set analysis

sunny_talwar — Thu, 15 Oct 2015 13:23:58 GMT

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)

Re: indirect set analysis

hic — Thu, 15 Oct 2015 13:24:02 GMT

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

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

HIC

Re: indirect set analysis

felcar2013 — Thu, 15 Oct 2015 13:29:11 GMT

thanks Henric

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

Felipe

Re: indirect set analysis

Qrishna — Thu, 15 Oct 2015 13:32:53 GMT

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

Re: indirect set analysis

felcar2013 — Thu, 15 Oct 2015 16:49:40 GMT

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

thanks

felipe

Re: indirect set analysis

felcar2013 — Thu, 15 Oct 2015 16:51:01 GMT

your proposal is correct, though the credit went to Henric,... i realized it late your answer came first

thanks!

felipe