Assign values in one table based on another table

LisaDrummond — Wed, 03 Nov 2021 21:32:25 GMT

I have 2 tables. One table contains Invoices that need to be assigned to a user based on data in another table.

Example Data:

Table A - Invoices

InvoiceId = 123456, Customer = A

InvoiceId = 456789, Customer = B

InvoiceId = 678910, Customer = A

Table B - Users

User ID = 1234, Customer =A, MaxRows = 30

UserID = 23456, Customer = B, MaxRows = 5

I need to assign each Invoice record to a user for that Customer, but limit the # of Invoices that get assigned based on the MaxRows value for each user.

Any ideas? I have about 1,900 records per day so the dataset isn't very large.

Re: Assign values in one table based on another table

BenjaminT — Thu, 04 Nov 2021 19:45:07 GMT

Hi,

Interesting problem! My approach to this would be to assign a counter ID for each Invoice based on the Customer and row number. So the very first invoice for Customer A would be 'A1', and so on. You could do this with a combination of Peek() and RangeSum() functions as explained in this blog:

https://community.qlik.com/t5/Qlik-Design-Blog/Counters-in-the-Load/ba-p/1464117

I would then by expand out the Users tables, so that there are 30 rows for User ID =1234, Customer =A. and 5 rows for User ID =23456, Customer =B, and perform the same counters, A1, A2, A3 etc up to A<MaxRows>. You can user the IterNo() function to do this expansion.

Once this is done the two tables will associated with each other on the counter ID field. (or you could join them if you wanted to)

See attached qvf file, I hope this help you to find a solution even if this isn't exactly it.

Thanks

Ben

topic Re: Assign values in one table based on another table in Connectivity & Data Prep

Assign values in one table based on another table

Re: Assign values in one table based on another table