Need IDs based on a condition fulfilled in a different record

[Shikhar_Diagonal]

Dear Community,

I have the following dataset-

Opportunity ID	Record Date	Stage	Change in Stage	Order Value	Change in Order Value
1	05-07-2023	20	NA	10000	NA
1	06-07-2023	40	Change	10500	Change
2	05-07-2023	40	NA	5000	NA
2	06-07-2023	40	No Change	4000	Change
3	05-07-2023	80	NA	4000	NA
3	06-07-2023	90	Change	4000	No Change
4	05-07-2023	40	NA	20000	NA
4	06-07-2023	40	No Change	20000	No Change

Note: Dates are in DD-MM-YYYY format

PROBLEM STATEMENT:

My requirement is to fetch the records of opportunities from the above table where either a Change in Stage or a Change in order value has occurred. But I don't only need the record in which the change has happened, but also the record of the previous day for the same Opportunity ID.

How can I achieve this in the back end or in the front end in Qliksense?

For reference, I am attaching the desired output table as well-

Opportunity ID	Record Date	Stage	Change in Stage	Order Value	Change in Order Value
1	05-07-2023	20	NA	10000	NA
1	06-07-2023	40	Change	10500	Change
2	05-07-2023	40	NA	5000	NA
2	06-07-2023	40	No Change	4000	Change
3	05-07-2023	80	NA	4000	NA
3	06-07-2023	90	Change	4000	No Change

P.S.- This can be achieved in SQL by running the following query -

Select * FROM Table1 where OpportunityID in (Select OpportunityID from Table1 where Change in Stage = 'Change' OR Change in Order Value = 'Change' and Record Date = '06-07-2023');

Thanks for your time,

Shikhar Jha

#DataModeling #QlikSense #QliksenseScripting

[sidhiq91 · Accepted solution]

@Shikhar_Diagonal  Please use the code below:

Temp:
LOAD
"Opportunity ID",
"Record Date",
Stage,
"Change in Stage",
"Order Value",
"Change in Order Value"
FROM [lib://Qlik Community Practice/Change in Orders.xlsx]
(ooxml, embedded labels, table is Sheet1);

inner join(Temp)
Load "Opportunity ID"
Resident Temp
where match("Change in Stage",'Change') or match("Change in Order Value",'Change');

Exit Script;

IF this resolves your issue, please like and accept it as a solution.

[Shikhar_Diagonal]

Hi Sidhiq,

Thanks for the solution. It solved the purpose.