Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to get value based on existence of record

I have three data sources. The first two are orders, and I'm using concatenate to create a single table (named "Orders") which is working fine. One of the two order data sources has a field named "order_status". The value of this field needs to be populated based on whether a record exists in the third data source with the common/lookup key being the order number.

So, for example, if the order number is 12345, and a record exists in my third data source for order 12345, the status should be "Filled". If there is no record in the third data source with number 12345, then the status should be "Open". What's the best way to populate the status in the load script? Or is there a different approach I should be using?

1 Solution

Accepted Solutions
MVP
MVP

Re: How to get value based on existence of record

Maybe create a mapping table from your third table first, like

MAP:

MAPPING LOAD

OrderNo, 'Filled'

FROM ThirdTable;

Orders:

LOAD

OrderNo,

ApplyMap('MAP', OrderNo, 'Open') as OrderStatus

FROM FirstSecondTable;

2 Replies
MVP
MVP

Re: How to get value based on existence of record

Maybe create a mapping table from your third table first, like

MAP:

MAPPING LOAD

OrderNo, 'Filled'

FROM ThirdTable;

Orders:

LOAD

OrderNo,

ApplyMap('MAP', OrderNo, 'Open') as OrderStatus

FROM FirstSecondTable;

Not applicable

Re: How to get value based on existence of record

I just found an example using ApplyMap as your reply came in and I was thinking "I think this is what I need", and your reply verified I was going in the right direction. Half the time searching for an answer is the hard part because you don't know what  you're searching for!

Community Browser