Qlik Conditional logic

[Shivam22]

Hi All,

Can you please help me understand how to resolve below 

ID	Name	Date	Product
123	John	1/1/2023	TV
234	John	2/1/2024	TV
345	John	2/2/2024	TV
456	John	3/4/2024	TV
567	John	3/6/2024	Speaker

I have to create two columns in above table with two conditions

Condition 1 - If John buying TV after one year from initial purchase and the next purchase after one year from last purchase then its New purchase  but if he changes Product like Speaker in the same Year then its New ,if not Existing - Always initial purchase is New

Condition 2 - If John buying TV or Speaker after one year from initial purchase and the next purchase after one year from last purchase then its New purchase if not Existing - Always initial purchase is New

Results Like below

ID	Name	Date	Product	Condition 1	Condition 2
123	John	1/1/2023	TV	New	New
234	John	2/1/2024	TV	New	New
345	John	2/2/2024	TV	Existing	Existing
456	John	3/4/2024	TV	New	New
567	John	3/6/2024	Speaker	New	Existing

Thanks in Advance!

[HirisH_V7]

Hi look into this,

LOAD
    ID,
    Name,
    Date(Date#(Date, 'M/D/YYYY')) as Date,
    Product,
    If((Date - Previous(Date)) >= 365, 'New', 'Existing') as New/Existing, 

    // Condition 1
    If(RowNo()=1, 'New',
        If(Product <> Previous(Product) and Year(Date)=Year(Previous(Date)), 'New',
        If(Year(Date)-Year(Previous(Date)) >= 1, 'New', 'Existing'))
    ) as [Condition 1],

    // Condition 2
    If(RowNo()=1, 'New',
        If(Year(Date)-Year(Previous(Date)) >= 1, 'New', 'Existing')
    ) as [Condition 2]

Resident YourTable
Order By Name, Date;

[HirisH_V7]

Did you find a solution to your question? Mark the solution as accepted and if you found it useful. else Continue the thread.

[mikekelvin]

Check out the script

// 1. Load the Data and Ensure Correct Date Formatting
TempData:
LOAD 
    ID,
    Name,
    Date(Date#(Date, 'M/D/YYYY')) as PurchaseDate, // Convert to Qlik Date Value
    Product
INLINE [
ID, Name, Date, Product
123, John, 1/1/2023, TV
234, John, 2/1/2024, TV
345, John, 2/2/2024, TV
456, John, 3/4/2024, TV
567, John, 3/6/2024, Speaker
];

// Sort the data by Name and then PurchaseDate
SortedData:
NoConcatenate
LOAD 
    ID,
    Name,
    PurchaseDate,
    Product
RESIDENT TempData
ORDER BY Name, PurchaseDate ASC; // **CRUCIAL STEP** for sequential logic

DROP TABLE TempData;

---

// 2. Apply Sequential Logic using Peek()

FinalData:
LOAD 
    ID,
    Name,
    Date(PurchaseDate) as Date, // Keep Date for display
    Product,
    
    // Get the previous Name, Date, and Product for comparison
    Peek('Name', -1, 'SortedData') as PrevName,
    Peek('PurchaseDate', -1, 'SortedData') as PrevDate,
    Peek('Product', -1, 'SortedData') as PrevProduct,
    
    // Check if this is the first purchase (GroupName check)
    // NOTE: Peek('Name', -1) will be null or different if this is the first record for a Name
    If(IsNull(Peek('Name', -1, 'SortedData')) OR Name <> Peek('Name', -1, 'SortedData'), 'New', 
    
        // Calculate the difference: 1 year must have passed (>= 365 days)
        If( PurchaseDate >= AddYears(Peek('PurchaseDate', -1, 'SortedData'), 1), 'New', 
        
            // Condition 1: Check for product change in the same year/less than a year
            // The purchase must be 'New' if the product changed AND the 1-year gap was NOT met.
            If( Product <> Peek('Product', -1, 'SortedData'), 'New', 'Existing')
        )
    ) as [Condition 1],

    // Condition 2: Simpler logic - only checks for the 1-year gap (Always 'New' initially)
    If(IsNull(Peek('Name', -1, 'SortedData')) OR Name <> Peek('Name', -1, 'SortedData'), 'New', 
        If( PurchaseDate >= AddYears(Peek('PurchaseDate', -1, 'SortedData'), 1), 'New', 'Existing' )
    ) as [Condition 2]

RESIDENT SortedData
ORDER BY Name, PurchaseDate ASC; // Maintain the sort order

DROP TABLE SortedData;

@HirisH_V7 , 

Some of the issues that has been resolved includes:

1. The If(IsNull(Peek('Name', -1, 'SortedData')) OR Name <> Peek('Name', -1, 'SortedData'), 'New', ...) block ensures that the first purchase for any customer is correctly labeled "New," preventing errors if you add more names later.

2. Using PurchaseDate >= AddYears(PrevDate, 1) correctly enforces the requirement that the purchase date must be on or after the date one year later, accurately representing "after one year from last purchase."

3. The product change check is nested correctly. It's only evaluated if the 1-year gap was NOT met, perfectly matching the rule: "but if he changes Product like Speaker in the same Year then its New, if not Existing."

Hope this answer suits your question.