Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marishnagendran
Creator
Creator

New Customer in Qliksense

Dear All,

Invoice_DateCustomer_NameProductQty
01-Apr-19AProduct 130
02-Apr-19BProduct 120
02-Apr-19AProduct 120
01-May-19AProduct 110
01-May-19CProduct 130
01-May-19DProduct 220
02-May-19BProduct 210
01-Jun-19AProduct 25
01-Jun-19BProduct 210
01-Jun-19EProduct 210


The above table is my transaction table

I want to find If the customer Name "A" billed in Apr Month of 2019 then i want to tag that customer as Apr-19 and the customer do his business on May,Jun,Jul,Etc then i want to tag that customer as Apr-19 Only.Like wise all the customer i want to do.

Kindly see below example

Invoice_DateCustomer_NameProductQtyNew Customer Tag
01-Apr-19AProduct 130Apr-19
02-Apr-19BProduct 120Apr-19
02-Apr-19AProduct 120Apr-19
01-May-19AProduct 110Apr-19
01-May-19CProduct 130May-19
01-May-19DProduct 220May-19
02-May-19BProduct 210Apr-19
01-Jun-19AProduct 25Apr-19
01-Jun-19BProduct 210Apr-19
01-Jun-19EProduct 210Jun-19

 

 

New customer Tag column i want to build in Transformation layer is it possible?

 

1 Solution

Accepted Solutions
colinh
Partner - Creator II
Partner - Creator II

Assuming your table is called "Invoices" then:

NewCustomerTag:
load Customer_Name,
monthname(min(Invoice_Date)) as [New Customer Tag]
Resident Invoices group by Customer_Name;

You can just leave that table separate, or if you want to join it onto Invoices as you have in your example then:

left join (Invoices)
load * Resident NewCustomerTag;

drop Table NewCustomerTag;

or you could left join to Customers if you have a Customers table.

 

 

View solution in original post

5 Replies
colinh
Partner - Creator II
Partner - Creator II

Assuming your table is called "Invoices" then:

NewCustomerTag:
load Customer_Name,
monthname(min(Invoice_Date)) as [New Customer Tag]
Resident Invoices group by Customer_Name;

You can just leave that table separate, or if you want to join it onto Invoices as you have in your example then:

left join (Invoices)
load * Resident NewCustomerTag;

drop Table NewCustomerTag;

or you could left join to Customers if you have a Customers table.

 

 

marishnagendran
Creator
Creator
Author

Thanks colinh it's working.

marishnagendran
Creator
Creator
Author

I have another doubt

If it is current financial year then i want separate a new customer as  Apr-19,May-19 like wise.

If it is previous year i want tag the customer only year (e.g) 2017 or 2018

colinh
Partner - Creator II
Partner - Creator II

I don't know what your financial year is, but something like

NewCustomerTag:

if(year([New Customer Tag]=year(today()), [New Customer Tag], year([New Customer Tag]) as [New Customer Tag];
load Customer_Name,
monthname(min(Invoice_Date)) as [New Customer Tag]
Resident Invoices group by Customer_Name;

should work for calendar year.

marishnagendran
Creator
Creator
Author

Our financial year is Apr to Mar