topic Re: Finding exiting and new Clients in App Development

Finding exiting and new Clients

Syedtahaali — Tue, 27 Dec 2022 07:25:00 GMT

I want to find a list of exit and fresh clients from customer ID by comparing current and previous month.

for example only a list of clients should appear if customer ID appears for the first time in the current month.

thank you

Re: Finding exiting and new Clients

sidhiq91 — Tue, 27 Dec 2022 07:26:49 GMT

@Syedtahaali Could you please post some sample data and also expected output?

Re: Finding exiting and new Clients

Syedtahaali — Tue, 27 Dec 2022 07:47:17 GMT

Yellow marked is a 'fresh client' in the current month which is may and red marked 'exit client'.

Re: Finding exiting and new Clients

BTIZAG_OA — Tue, 27 Dec 2022 09:18:25 GMT

Hello syedtahaali

You can try this hope it helps

inline: load * Inline [ month,customerID,ClientName,TotalAmount 31.03.2022,1,JPMorgan,27 31.03.2022,2,GoldmanSachs,800 30.04.2022,1,JPMorgan,27 30.04.2022,2,GoldmanSachs,800 30.04.2022,3,MorganStanley,25600 31.05.2022,1,JPMorgan,27 31.05.2022,2,GoldmanSachs,800 31.05.2022,4,SPGlobal,258000 ]; temp: Load max(month) as currentmonth Resident inline; let vCurrentDate = date(peek('currentmonth',0,'temp')); drop Table temp; Trace CurrentDate: $(vCurrentDate); table1: Mapping load customerID, 'Current Customer' as CustomerStatus Resident inline Where month < '$(vCurrentDate)'; table2: Mapping load customerID, 'Current Customer' as CustomerStatus Resident inline Where month = '$(vCurrentDate)'; data: Load month, customerID, ClientName, TotalAmount, ApplyMap('table1',customerID,'New Customer') as applymap Resident inline where month = '$(vCurrentDate)'; Concatenate(data) Load month, customerID, ClientName, TotalAmount, ApplyMap('table2',customerID,'Exit Customer') as applymap Resident inline where month < '$(vCurrentDate)'; drop table inline;

Re: Finding exiting and new Clients

Syedtahaali — Tue, 27 Dec 2022 09:31:02 GMT

Thank you so much for your response, but the result I'm looking for is shown in the picture below

I want this table to appear on a new table chart, labeled as 'Fresh clients' which was present is current month.

also I want this table to appear on a new table chart, labeled as 'Exit clients' which was not present is current month.

Re: Finding exiting and new Clients

BTIZAG_OA — Tue, 27 Dec 2022 09:44:20 GMT

Hello,

You can easily achieve this with selections drag&drop a filter pane to your sheet and select 'New Customer' or 'Exit Customer'

Re: Finding exiting and new Clients

Syedtahaali — Wed, 28 Dec 2022 12:23:33 GMT

I have followed your query but was unable to get the required result

kindly check If I have done it correctly or am I missing something.

data: LOAD Distinct Months, UID, "Code", "Standard Name", "Group", RR, BB, A_limit, B_Limit, [A+B]_Limit, A_OS, B_OS, [A+B]_OS FROM [lib://Data inputs/my input.qvd] (qvd); temp: Load max(Months) as currentmonth Resident data; let vCurrentDate = date(peek('currentmonth',0,'temp')); drop Table temp; Trace CurrentDate: $(vCurrentDate); table1: Mapping load UID, 'Current Customer' as CustomerStatus Resident data Where Months < '$(vCurrentDate)'; table2: Mapping load UID, 'Current Customer' as CustomerStatus Resident data Where Months = '$(vCurrentDate)'; Consolidateddata: LOAD Months, UID, "Code", "Standard Name" , "Group", RR, BB, A_limit, B_Limit, [A+B]_Limit, A_OS, B_OS, [A+B]_OS ApplyMap('table1',UID,'New Customer') as applymap Resident data where Months = '$(vCurrentDate)'; Concatenate(Consolidateddata) Load Months, UID, "Code", "Standard Name" , "Group", RR, BB, A_limit, B_Limit, [A+B]_Limit, A_OS, B_OS, [A+B]_OS ApplyMap('table2',UID,'Exit Customer') as applymap Resident data where Months < '$(vCurrentDate)'; drop table data;

Re: Finding exiting and new Clients

BTIZAG_OA — Wed, 28 Dec 2022 12:42:07 GMT

Hello its hard to find out whats wrong from here, can you provide excel/QVD file or the script log that you ran.