Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Syedtahaali
Contributor II
Contributor II

Finding exiting and new Clients

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 

 

 

Labels (1)
7 Replies
sidhiq91
Specialist II
Specialist II

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

Syedtahaali
Contributor II
Contributor II
Author

Qlik forum.png

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

 

BTIZAG_OA
Creator
Creator

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;

Capture.PNG 

Syedtahaali
Contributor II
Contributor II
Author

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

Expected output.png

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
Creator
Creator

Hello,

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

Syedtahaali
Contributor II
Contributor II
Author

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
Creator
Creator

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