Count Active Customers

[#1]

Hi All,

A very quick question. I have a client master list with client numbers (which is incomplete) and a database with a list of client transactions. I need to count the number of active clients that I have on board. Active clients is defined as all clients on the client master list + any clients in the transaction database that has sales in the year 2014, but are not in the master client list. I have attached some dummy data. (formula in a simple text box will be ok)

Any assistance appreciated.

Thank you

H

[ali_hijazi]

in your script get the clients in the transactions table that are not in your master list using where not exists

then concatenate the result to the master list of the clients

[datanibbler]

Hi,

just do a FULL/ OUTER/ WHATEVER JOIN (it's OUTER in QV, judging from the help) between the two tables, using your Customer_ID as join_field. That should return a complete list of all customers that are in either of the tables.

[#4]

Hi Try it

Customer:

LOAD CID,

null() as Tran_Date

FROM

(ooxml, embedded labels, table is Customer);

Cust_Tran:

LOAD CID,

     Tran_Date,

     Year(Tran_Date) as Year

FROM

(ooxml, embedded labels, table is Cust_Tran)

where Year(Tran_Date)=2014;

[DavidFoster1]

Yet another alternative for you to consider.

1)     Create a mapping table of distinct customers from your transaction table with the second column being 1.

2)     On your customers table use an applymap with a default of 0.

This will give you a 1/0 flag field for active customers.

[#6]

If you can assign your master list clients [Transaction Date] as 0 in load script, then just

Count({<Year={2014,0}>} distinct [Client No])

That is all because NULL handling is a real mess in Qlikview... (Tell me somebody, why it is so?)

[#7]

By the way, can you post your sample xlsx file as well?

[#8]

Thanks Alexander,

Please find attached

H

[#9 · Accepted solution]

Like that probably...

[#10]

If your problem is solved, please close the topic and choose the correct solution.