Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld May 10-12, Online and Free! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
FarmerTom
Contributor
Contributor

classify the sales proposal as won/lost based on an event log

Hello,

I have a simple timeseries data representing a sales event log. I would like to create a new column in script editor that classifies the given Ops id as WON, LOST or PENDING based on the below conditions:

IF for a given Ops id a "Deal signed" milestone exists, the new column (status) should return WON for all of the Ops id records

IF for a given Ops id a "Opportunity cancelled" milestone exists, the new column should return LOST for all of the Ops id records

IF none of the above, then new column sould return PENDING

Excel example attached, will appreciate any advice

1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

Try the following load script:

WonOrLost:
LOAD
[Ops id],
Milestone,
Date(Time,'MM/DD/YYYY') as Time,
[Sales Manager],
[Client ID],
Join,
Status
FROM [lib://QlikSenseApps/WONorLOST.xlsx]
(ooxml, embedded labels, table is Sheet1);

Ops:
Load distinct [Ops id]
Resident WonOrLost;

Join (Ops)
Load [Ops id],
max(Time) as DealWon
Resident WonOrLost
where Milestone = 'Deal signed'
group by [Ops id];

join (Ops)
Load [Ops id],
max(Time) as OpportunityCanceled
Resident WonOrLost
where Milestone = 'Opportunity cancelled'
group by [Ops id];

join (Ops)
Load [Ops id],
if(not isnull(DealWon),'Won',if(not isnull(OpportunityCanceled),'Lost','Pending')) as Classification
Resident Ops;

drop fields DealWon, OpportunityCanceled;

//The following code will add the field by to the WonOrLost table.  If you have an Ops dimension table, you may want to add it to that table instead.

Left join (WonOrLost)
Load [Ops id],
Classification
Resident Ops;

drop table Ops;

View solution in original post

2 Replies
GaryGiles
Specialist
Specialist

Try the following load script:

WonOrLost:
LOAD
[Ops id],
Milestone,
Date(Time,'MM/DD/YYYY') as Time,
[Sales Manager],
[Client ID],
Join,
Status
FROM [lib://QlikSenseApps/WONorLOST.xlsx]
(ooxml, embedded labels, table is Sheet1);

Ops:
Load distinct [Ops id]
Resident WonOrLost;

Join (Ops)
Load [Ops id],
max(Time) as DealWon
Resident WonOrLost
where Milestone = 'Deal signed'
group by [Ops id];

join (Ops)
Load [Ops id],
max(Time) as OpportunityCanceled
Resident WonOrLost
where Milestone = 'Opportunity cancelled'
group by [Ops id];

join (Ops)
Load [Ops id],
if(not isnull(DealWon),'Won',if(not isnull(OpportunityCanceled),'Lost','Pending')) as Classification
Resident Ops;

drop fields DealWon, OpportunityCanceled;

//The following code will add the field by to the WonOrLost table.  If you have an Ops dimension table, you may want to add it to that table instead.

Left join (WonOrLost)
Load [Ops id],
Classification
Resident Ops;

drop table Ops;

View solution in original post

FarmerTom
Contributor
Contributor
Author

Works like a charm, thanks!