Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Like that probably...
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
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.
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;
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.
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?)
By the way, can you post your sample xlsx file as well?
Thanks Alexander,
Please find attached
H
Like that probably...
If your problem is solved, please close the topic and choose the correct solution.