Finding exiting and new Clients

[Syedtahaali]

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 

[sidhiq91]

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

[Syedtahaali]

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

[BTIZAG_OA]

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;

 

[Syedtahaali]

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.

[BTIZAG_OA]

Hello,

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

[Syedtahaali]

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;

[BTIZAG_OA]

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