topic Re: Best method to create a new conditional field using Data flow in Connectivity & Data Prep

Best method to create a new conditional field using Data flow

allydaniels — Sun, 27 Apr 2025 05:26:28 GMT

Hello Qlik community

I am hoping to use Data flow to create a new field to capture "New Customers" as a column.

For example , I would like to be able to filter customers who have only purchased products from the last 3 months or only this year 2025 as "new customers", and the rest as "previous customers".

How might I design this new field using processors via Data flow (as I am joining this table with other datasets as well)?

I've tried aggregation, but it seems I unable to restrict dates and sales. Is there a non-script way to go about this?

Thank you!

Re: Best method to create a new conditional field using Data flow

mshann01 — Mon, 05 May 2025 13:33:25 GMT

You'd want to create a separate table/extract of just the records in the past 3 months. Then join that new table/file to the original one on your Customer Name and create the column if there's a match.

Re: Best method to create a new conditional field using Data flow

stevedark — Thu, 08 May 2025 12:56:25 GMT

Hi @allydaniels

This would be super-simple in Qlik Load Script. Is there a reason that it must be done using Flow?

Customer:
LOAD
*,
if([Is New Customer] = 'Yes', Customer, null()) as [New Customers]
;
LOAD
*,
if([First Purchase] > AddMonths(Today(),-3), 'Yes', 'No;) as [Is New Customer]
;
LOAD
Customer,
Date(min([Sale Date]), 'DD MMM YYYY') as [First Order],
Date(max([Sale Date]), 'DD MMM YYYY') as [Last Order],
sum(Value) as [Total Customer Sales]
RESIDENT Orders
GROUP BY Customer
;

As well as letting you have a separate dimension of new customers you might then also use this aggregated customer table to look at other things. You could look for customers with aggregated sales of over X in the past 12 months who have not placed an order in the past 3, for instance.

Hope that is useful.

Steve