topic Re: Not active Clients? in QlikView

Not active Clients?

Wed, 25 Nov 2020 16:16:04 GMT

Hi fellow qlikviewers.

I have a yearly .QVD which I use for statistics, that I have scheduled to reload every night with new data from the database.

With this I would like to see maybe which client that has NOT been active for the last 7 or 30 days.

Is it doable in Qlikview?

Re: Not active Clients?

MK_QSL — Thu, 23 Jun 2016 12:27:36 GMT

Yes... Provide sample data to work..

Re: Not active Clients?

Ricardo_Gerhard — Thu, 23 Jun 2016 12:34:36 GMT

Hi Niklas,

There is a Qlikview Governance Dashboard that read all log files and you can see your users access, aplications complexity, and a lot of informations.

So, if you want to analyze your clients access on your system, you need to load this data on your application with a field like "Last_Used"/"Last_Access" and create a "<" expression.

Does it help you?

Regards.

Re: Not active Clients?

Thu, 23 Jun 2016 12:35:23 GMT

Analysis:
LOAD TradeDate,
AccountId,
MarketId,
ExecutionVenueId,
ValueName,
CurrencyId,
Value,
ValueSEK
FROM

(qvd);

Accounts:
LOAD AccountId,
ClientId,
AccountShortName,
AccountLongName,
ParentAccountId,
Type,
CountryISOCode,
City,
Manager,
ManagerId,
Trader,
TraderId,
RegisteredDate,
DeregisteredDate,
Sales,
SalesId,
BackupTrader,
BackupTraderId
FROM

(qvd);

Notes:
LOAD AccountDate,
AccountId,
Cancellation,
Commission,
CSA,
CurrencyId,
IsBuy,
ISIN,
MarketId,
NoteId,
Price,
SettleDate,
ShortName,
TradeDate,
Volume
FROM

(qvd);

Trades:
LOAD AccountId,
AccountRef,
ClientId,
CounterpartId,
CurrencyId,
ExecutionVenueId,
IsBuy,
ISIN,
LiquidityIndicator,
MarketOrderId,
OrderbookId,
OrderId,
Price,
ShortName,
TradeDate,
TradeId,
Volume
FROM

(qvd);

Re: Not active Clients?

Peter_Cammaert — Thu, 23 Jun 2016 13:04:19 GMT

If it's not document access that you want to monitor, but simply the most recent activity by customers as stored in your database, then what field can be used to figure out the last activity date?

Peter

Re: Not active Clients?

Thu, 23 Jun 2016 13:08:38 GMT

Accountid, TradeDate I would say. What accountId has not been active the last 7 dates based on tradedate

Re: Not active Clients?

Ricardo_Gerhard — Thu, 23 Jun 2016 13:33:33 GMT

Niklas, you can filter users simplely typing

TradeDate<"d-7".

Try it.

Re: Not active Clients?

Peter_Cammaert — Thu, 23 Jun 2016 13:49:13 GMT

Since you reload the data every day, add this to the bottom of your example script:

LEFT JOIN (Accounts)

LOAD AccountId,

IF (Max(TradeDate) < Today()-7, 1, 0) AS InactiveInLast7Days

RESIDENT Trades

GROUP BY AccountId;

If you don't want this flag to become part of your Accounts table, LEFT JOIN the load into Trades itself.

Best,

Peter

[Edit] The JOIN/GROUP BY field is called AccountId, not AccountID

Re: Not active Clients?

Mon, 27 Jun 2016 11:11:10 GMT

I see you understand what im looking for. But the syntax is incorrect getting "Invalid expression" message when I run the script.

Re: Not active Clients?

Peter_Cammaert — Mon, 27 Jun 2016 11:54:16 GMT

And you want me to guess what syntax you are using? That may cost us both a lot of time. Please post that part of your script that contains the base table Load and the JOIN you get errors on.

It works for me:

Best,

Peter

[Edit] Sorry, you're right. I used the wrong table name. Replace Accounts with Notes in the LEFT JOIN clause.

Re: Not active Clients?

Peter_Cammaert — Mon, 27 Jun 2016 12:04:19 GMT

And you may want to overhaul your entire script to store Notes & Trades in a single table where you discern between the two by using a text field that contains either 'Note' or 'Trade'. That way you'll determine the last TradeDate from both data sets instead of from a single one.

Peter

Re: Not active Clients?

Mon, 27 Jun 2016 13:12:46 GMT

Thank you very much this is exactly what I was looking for. Again I love this community! Thank you

Re: Not active Clients?

Mon, 27 Jun 2016 13:16:19 GMT

I see, but these are from 2 different tables in a database. How would you join those 2 into one big facttable. I understand that would bee much smoother, but I don't know how to do that.

Re: Not active Clients?

oknotsen — Mon, 27 Jun 2016 13:33:28 GMT

If your question is now answered, please flag the Correct Answer.

If not, please make clear with which part of this topic you still need help .

Re: Not active Clients?

Mon, 27 Jun 2016 13:45:44 GMT

i did mark as helpful.

Is it possible to instead of using -31 days or -7 days to use the mastercalendar that I have included to the report already?

Re: Not active Clients?

Peter_Cammaert — Mon, 27 Jun 2016 13:53:45 GMT

Do you mean you want to make the comparison date (now Today()) variable and compare last customer activity to a date selected in the Calendar?

Re: Not active Clients?

Mon, 27 Jun 2016 14:14:59 GMT

Exactly; sorry for my terrible english.

Re: Not active Clients?

Peter_Cammaert — Mon, 27 Jun 2016 14:29:43 GMT

That cannot be prepared in your load script.

Re: Not active Clients?

Mon, 27 Jun 2016 14:44:51 GMT

Ok this works fine thank you, Peter!