topic Re: Enriching data with other records in QlikView

Enriching data with other records

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

Good Day! A little stuck with how to approach this. I have a data set:

Load Account, Person, Product, Ticket;

SQL Select*

From Accounts

The Accounts database only populates [Ticket] for the [Person]&[Product] combination, so it is not always populated for each [Account Record].

When loading, I would like to populate the same [ticket] number where that same [person]&[product] combination exist in another table. Example

Account|Person|Product|Ticket

A111|Jane|CAD|123456

A222|Jane|CAD|0

Should be:

A111|Jane|CAD|123456

A222|Jane|CAD|123456

Do I need to load a temp table while omitting the 0/null records? Or is there an easier way?

Thanks

Re: Enriching data with other records

Arthur_Fong — Fri, 06 Dec 2019 03:28:18 GMT

You can try join these two tables if person and product columns are the unique keys for joining.

After joining, order this joined table by person and product in ascending order.

Then, try to create this new column:

if((Ticket=0 or len(Ticket)<1 or isnull(Ticket)),if(peek(person)=person and peek(product)=product,peek(NewTicket),Ticket)) as NewTicket

Re: Enriching data with other records

Brett_Bleess — Mon, 16 Dec 2019 19:35:48 GMT

Jim, did Arthur's post help you get a working solution? If so, please do not forget to return to your post here and use the Accept as Solution button on Arthur's post to give him credit and let other Community Members know this worked for you. If you are still working on things, leave an update with what you still need.

The only other place I could point you that might have something you could use is the Design Blog area of Community:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have anything better for you.

Regards,
Brett