topic Re: Flag cust ID by date in load statement in QlikView

Flag cust ID by date in load statement

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

Please help me to flag old customer by year. attached qvw for your rerence.

CustID	Date	Flag
1	12/7/2015
2	1/25/2016
3	1/24/2017
4	2/16/2017
6	3/3/2017
7	11/7/2017
2	01/02.2018	' < 2 year Old Customer'
9	01/07.2018	New Customer
4	01/11.2018	'<1 Year Old Customer'

Inventry.xlsx

Re: Flag cust ID by date in load statement

sunny_talwar — Tue, 16 Jan 2018 16:34:09 GMT

Do you just need 3 flags or there are multiple flags like ❤️ years, <4 years, <10 years.... etc?

Re: Flag cust ID by date in load statement

karan_kn — Tue, 16 Jan 2018 16:44:46 GMT

Thanks for response Sunny, I need 3 flags like <=1 year, >=2 and <=3 year, >=4 and <=5 year, and New CustID

Re: Flag cust ID by date in load statement

sunny_talwar — Tue, 16 Jan 2018 16:49:09 GMT

Isn't this the same thing as to what we were trying to do here?

Aggregate function year to month

I am not able to understand what you are trying to do and what issues you are running into. I think you need to elaborate and possible provide more details Karan

Re: Flag cust ID by date in load statement

karan_kn — Tue, 16 Jan 2018 16:50:44 GMT

Yes, the same. it would be useful aggregate by month

Re: Flag cust ID by date in load statement

sunny_talwar — Tue, 16 Jan 2018 16:53:10 GMT

I see what you mean... try this

Fact:

LOAD CustID,

Sales_Date,

(Year(Sales_Date)*12) + Month(Sales_Date) as MonthYear,

...

FROM ...;

Left Join (Fact)

LOAD Cust_ID,

If(MaxMonthYear- MaxMonthYear2 = 12, 'AAA',

If(MaxMonthYear- MaxMonthYear2 <= 36, 'BBB',

If(MaxMonthYear- MaxMonthYear2 <= 60, 'CCC'))) as Flag;

LOAD Cust_ID,

Max(MonthYear) as MaxMonthYear,

Max(MonthYear, 2) as MaxMonthYear2

Resident Fact

Group By Cust_ID;

Re: Flag cust ID by date in load statement

karan_kn — Tue, 16 Jan 2018 17:05:17 GMT

Is it possible to Flag new CusID's? Ex: CustID: 9, it's new ID, so flag to 'New Customer'

CustID	Date	Flag
1	12/7/2015
2	1/25/2016
3	1/24/2017
4	2/16/2017
6	3/3/2017
7	11/7/2017
2	01/02.2018	' < 2 year Old Customer'
9	01/07.2018	New Customer
4	01/11.2018	'<1 Year Old Customer'

topic Re: Flag cust ID by date in load statement in QlikView

Flag cust ID by date in load statement

2

01/02.2018

' < 2 year Old Customer'

9

01/07.2018

New Customer

4

01/11.2018

'<1 Year Old Customer'

Re: Flag cust ID by date in load statement

Re: Flag cust ID by date in load statement

Re: Flag cust ID by date in load statement

Re: Flag cust ID by date in load statement

Re: Flag cust ID by date in load statement

Re: Flag cust ID by date in load statement

2

01/02.2018

' < 2 year Old Customer'

9

01/07.2018

New Customer

4

01/11.2018

'<1 Year Old Customer'