Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have below columns in my table.
Purchaser ID |
Purchaser Name |
Sale ID |
Sale Date |
Sale Amount |
I need to create a new column where if the Purchaser has made a sale on any Sale Date, the column shows value 1 and so on.
Example, if Purchaser ID 25 has different Sale IDs in 1/20/2021, 2/5/2021, 3/14/2021, the new column must have 1,1,1 for each Sale ID-Sale Date-Purchaser ID combination
you can get the distinct of the ID and DATE, then add a rowno() this will assign a unique number per ID/Date combination (that is if you dont care that for one ID, it starts at 1 and another ID its starts at 1000). then inner join to your data.
@edwin Thanks. But I need them to have some value instead of 1 and 1000 so that I can count how many Distinct ID/Date/SaleID combinations are there, to show the frequent Purchaser IDs who make frequent sales.
If Purchaser ID -25 makes 3 sales across 3 different dates, and each row has value 1 assigned, then i can add them up in the end (1+1+1) and say that Purchaser ID 25 frequency is 3.
if what you really wanted was count the number of sales, use count(Sale Amount) maybe it is best to start with business requirements and not with what you think the solution is
Count(Distinct SaleDate)
Hi @qlikwiz123
You can create a new field
Purchaser ID |
Purchaser Name |
Sale ID |
Sale Date |
Sale Amount |
1 as NoOfSales |
In front end, you can use
sum(NoOfSales)