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: 
liviumac
Creator
Creator

Help with duplicate names associated to unique customer numbers

Hello, here is my problem

each month I load the same database structure but obviously with different data

among the fields I load, there are a customer number and a customer name

the number stays the same, but the names occasionally change a character or they change completely (dont' ask :-),  so after loading 1-2 years worth of monthly data, I end up with a database which has unique customer numbers, but not unique customer names

and this is a problem because for data analysis we work with customer names, not numbers, because they are easier to remember

so if I select a certain customer name and want to see, for example, monthly sales for the past 12 months, I occasionally get no sales for the period before or after the name change, which is obviously wrong since I still have that customer, it's only that it changed names

so what I want to have is some sort of script or automated procedure that ensures that a customer number has an unique name associated to it at all times in the database and that name needs to be the latest name available

the issue is complicated by the fact that the population of customers is not stable from month to month, i.e. new customers may arrive, old customers may disappear but they are still relevant for analyses over longer periods of time

thank you

Liivu

2 Solutions

Accepted Solutions
StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

try to use the function FirstValue with group by

example

Load

id,

FirstValue(name) as name

Resident table

group by id;

View solution in original post

Vegar
MVP
MVP

You can try something  like this.

 

Customer:

Load customerno, customername, customerno as _cno

Resident transactions

Order by date desc

Where not exists(_cno, customerno)

;

Drop field _cno;

Drop field customername in transactions;

 

 

You can choose whether to keep this as a dimension table in your data model or to join this table back into your original transaction table.

View solution in original post

5 Replies
liviumac
Creator
Creator
Author

so I thought of the following algorithm for solving this:

1. load all distinct pairs of customers numbers and names, but I need to load starting from the latest month

2. if the number already exists, do not load a second pair of number and name

3. either create a separate table with numbers and names or use applymap for my current main table

 

problem is I do not know how to write this script

thank you

StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

try to use the function FirstValue with group by

example

Load

id,

FirstValue(name) as name

Resident table

group by id;

Vegar
MVP
MVP

You can try something  like this.

 

Customer:

Load customerno, customername, customerno as _cno

Resident transactions

Order by date desc

Where not exists(_cno, customerno)

;

Drop field _cno;

Drop field customername in transactions;

 

 

You can choose whether to keep this as a dimension table in your data model or to join this table back into your original transaction table.

liviumac
Creator
Creator
Author

@ StarinieriG 

thank you very much, this works, just needed to insert an order by date clause otherwise it brings the oldest name, not the newest

 

liviumac
Creator
Creator
Author

@Vegar 
thank you very much 
your script also works, just needed to change the order of "where" and "order" clauses and also "drop field FROM transactions" instead of "IN transactions"

once again thank you to both of you, this forum is awesome and so is QliK 🙂